Forum Discussion
Solved
Which best choice: Flow to get data from Lookup Column or Additional Column from Lookup Column?
If I have some List like: - 1.1 Country: Country ID, Country Name - 1.2 Purpose: Purpose ID, Purpose Name - 2. Product: Product ID, Product Name, Country ID, Country Name, Purpose ID, Purpose Name...
- OK, if different permissions apply for each stage separate lists makes sense. Also your choice for not using Managed Metadata Term Sets makes sense if additional data needs to be stored for each item.
Changing a List Item's content type won't cause loosing your data if your content types share the same Site Columns.
Modeling a relational data model in SharePoint this way is certainly possible to some end, but comes with challenges as you're experiencing at first hand now. In the end you might be better off modeling your data in a traditional relational database system (Azure SQL DB for example) and exposing it in SharePoint as an app. A PowerApp might work or a Provider-hosted add-in would provide the most flexibility...
ca_rem I see the technical limitation you're running into, but I'm not completely sure I understand your scenario. If I understand correctly an easier to achieve approach might be to define separate Content Types for each of the stages in your process. If your content types share the common columns of the content type representing the previous stage it's easy to change the Content Type of a List Item (effectively processing to the next stage) without losing any data.
Does this makes sense?
Yes, you already understand my problem. I set up the common column share between my content type, but I don't know how to refer to the columns correctly?
I mean, which I can use: Lookup Column and collect Additional Column follow this Lookup Column or using Flow and only one Lookup Column to get the ID, then fill up other columns by data pulled by Flow. (I have an example about this method here)
- In the approach I suggested you don't need separate lists for each stage, only separate Content Types within the same list. You can change stages of an item in the list just by changing it's Content Type. With such an approach you won't need as much lookup columns and no flows are needed to copy over data between lists.
I also think you should consider storing your data for Country, Purpose and maybe even Product in a Managed Metadata Term Set instead of SharePoint Lists.paulpascha I never try this approach before. But if I change the content Type of Item, does the content belongs to the previous content type lost?
And I need Lists to have difference Permission.
I also think you should consider storing your data for Country, Purpose and maybe even Product in a Managed Metadata Term Set instead of SharePoint Lists.
Yes, I considered to store that data in Managed Metadata Term Set, but I need some column which belongs to this data too, like:
Country: Country ID, Language, Nationality, Capital...
Purpose: Purpose ID
Product: Country ID, Purpose ID (Product ID = Country ID + Purpose ID)
- OK, if different permissions apply for each stage separate lists makes sense. Also your choice for not using Managed Metadata Term Sets makes sense if additional data needs to be stored for each item.
Changing a List Item's content type won't cause loosing your data if your content types share the same Site Columns.
Modeling a relational data model in SharePoint this way is certainly possible to some end, but comes with challenges as you're experiencing at first hand now. In the end you might be better off modeling your data in a traditional relational database system (Azure SQL DB for example) and exposing it in SharePoint as an app. A PowerApp might work or a Provider-hosted add-in would provide the most flexibility...