SOLVED

Which best choice: Flow to get data from Lookup Column or Additional Column from Lookup Column?

Brass Contributor

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, Price

- 3. Sale Order: SO number, Product ID, Country ID, Purpose ID, Price

- 4. Process Tracking: Process ID, SO number, Product ID, Country ID, Price, Process Status

- 5. Delivery Tracking: Process ID, Product ID, Delivery TIme

 

I mean, these lists describe a Process. The following lists will use the data in the previous lists.

I very confuse that I should use Flow or Additional Lookup Column to get the data from the previous Lists into the List after.

I think:

- If we using Additional Column from Lookup Column, we can't use Lookup Column again in the Lists after to get these data (Lookup Column doesn't support Lookup Column). But we can use flow to get data at the step we are stuck with, right?

=> So, Should we only use Flow with 1 Lookup Column to collect right ID Item from another List, and fill the data in the list we need? But with this approach, we must create each Flow for each Lookup Column we create, It's quite cumbersome and time-consuming. And the flows need time to finish their job, so the data is not fill up right the time we finish the form.

In my example, Can you show me the steps I should take in each list above?

6 Replies

@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.

@Paul Pascha 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)

best response confirmed by ca_rem (Brass Contributor)
Solution
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...

@Paul Pascha . 

I am very eager to see the combination of SharePoint and SQL, which may be the best method for this case. But now I only have time to focus on SharePoint.
Thanks for your help in all these days. Glad to know you.

1 best response

Accepted Solutions
best response confirmed by ca_rem (Brass Contributor)
Solution
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...

View solution in original post