Create dependency between two dropdowns within two different SharePoint list

[vsolanon]

Hi Community!

I am creating two SharePoint lists that are connected to a PowerApps application. The SP List1 "TripsList" shows all the trips a person can choose, and the SP List2 "Destinations", shows all the destination a person might be within a trip.

The relationship is one to many since one person might be in more than one destination during the same trip. For this, I created a column named "Identifier" which connects both lists. In the SP List1 the "Identifier" will be used only once, however, in the SP List2, it might be used more than once.

Both lists have a column named "Trip Status" with the following options: Cancelled, Postponed, In Process and Completed.  The end-user must select the trip status in each destination in the SP List2. 

I would like to create a rule to gather the general "Trip Status" of each trip in SP List1, based on the trip status the end-user select in the SP List2.

For example, if all the destinations in SP List2 for the identifier 1111 are marked as Completed, then the "Trip Status" on the SP List1 for the identifier 1111 must be Completed as well. 

Any suggestion is much appreciated! 

Thank you in advance 🙂

[v-xida-msft]

Hi @vsolanon ,

Could you please share a bit more about your scenario?

Do you want to change the "Trip Status" in your List 1 based on the "Trip Status" of all destinations with same identifier in your List 2 has been changed?

Could you please share a bit more about the  "Trip Status" column? Is it a Choice type column?

Based on the needs that you mentioned, I think there are two ways to achieve your needs -- Using PowerApps app or Power Automate flow.

1. If you want to achieve your needs in PowerApps canvas app

After you changed the "Trip Status" value for a specific destination in your List 2, you need to execute the following formula:

I assume that you use EditForm to edit the "Trip Status" value for a specific destination in your List 2, please set the OnSuccess property of the Edit form (EditForm1) to following:

If(
   IsBLank(LookUp('Destinations List', 'Trip Status'.Value <> "Completed" && Identifier = EditForm1.LastSubmit.Identifier)),
   Patch(
          'TripsList',
          LookUp('TripsList', Identifier = EditForm1.LastSubmit.Identifier),
          {
            'Trip Status': {
                              Value: "Completed"
                           }
          }
   )
);
Back()

Note: I assume that the EditForm1 connects to your "Destinations" list

2. If you want to use Power Automate flow to achieve your needs (I think it would be easier than above solution😞

Within the "Filter array" action, type the following expression:

@and(equals(item()?['Identifier'], triggerBody()?['Identifier']),not(equals(item()?['TripStatus']?['Value'], 'Completed')))

Within the Condition action, Left input field set to following expression:

length(body('Filter_array'))

Within the "Filter array 2" action, type the following expression:

@equals(item()?['Identifier'], triggerBody()?['Identifier'])

Within the "Update item" action, Id field set to following:

item()?['ID']

Title field set to following:

item()?['Title']

Please take a try with above solution, then check if the issue is solved.

Best regards,

[vsolanon]

Hi @v-xida-msft thank you for your response!

Kindly note I want to change the "Trip Status" in List 1 based on the "Trip Status" of all destinations with the same identifier in List. The "Trip Status" column is a Choice type column with the options: Cancelled, Postponed, In Process and Completed.

I took a try to the propose solution, however, it did not work as expected. I would like to create some rules in order to determine the value that the "Trip Status" for List1 will have. During the testing, all the items in List1 turn to be marked as "Completed" under the Trip Status column, even though their status in List2 was marked as Cancelled. 

I was wondering if it was possible to create 3 rules in the system as follows: 

     -If all items under Destination List (List2) (with the same Identifier) have the same option from the "Trip Status" column, then in List1 I expect the item (with this same Identifier) has the same value. For example, if there are 3 items under Destination List (List2) marked all as Cancelled, I hope the item in List1 is marked as Cancelled as well.

    -If there is at least one item under Destination List (List2) marked as "In Process", the item in List1 is marked as "In Process" because there is at least one item pending for this same Identifier.

   -If there is at least one item under Destination List (List2) marked as "Postponed" but no and there are no other items in Destination List (List2) marked as "In Process", the item in List1 should be marked "Postponed" because there is at least one item postponed for this same Identifier. This rule must exclude having an item marked as In Process in List 2.

I apologize for the long text, just try to share more information about the scenario expected 🙂 

[v-xida-msft]

Hi @vsolanon ,

Based on the needs that you mentioned, I think multiple If formula could achieve your needs. I have made a test on my side, please consider take a try with the following workaround:

Set the OnSuccess property of the Edit form to following formula:

If(
   // Third Rule
   CountRows(Filter('Destinations List', 'Trip Status'.Value = "Postponed" && Identifier = EditForm1.LastSubmit.Identifier))>=1 && CountRows(Filter('Destinations List', 'Trip Status'.Value = "In Process" && Identifier = EditForm1.LastSubmit.Identifier))=0,
   Patch(
          'TripsList',
          LookUp('TripsList', Identifier = EditForm1.LastSubmit.Identifier),
          {
            'Trip Status': {
                              Value: "Postponed"
                           }
          }
   ),
    // Second Rule
    CountRows(Filter('Destinations List', 'Trip Status'.Value = "In Process" && Identifier = EditForm1.LastSubmit.Identifier))>=1,
    Patch(
          'TripsList',
          LookUp('TripsList', Identifier = EditForm1.LastSubmit.Identifier),
          {
            'Trip Status': {
                              Value: "In Process"
                           }
          }
   ),
   // First Rule
   CountRows(Distinct(Filter('Destinations List', Identifier = EditForm1.LastSubmit.Identifier),'Trip Status'.Value))=1,
   Patch(
          'TripsList',
          LookUp('TripsList', Identifier = EditForm1.LastSubmit.Identifier),
          {
            'Trip Status': {
                              Value: First(Distinct(Filter('Destinations List', Identifier = EditForm1.LastSubmit.Identifier),'Trip Status'.Value)).Result
                           }
          }
   )
);
Back()

Please take a try with above solution, check if the issue is solved.

Best regards,

[vsolanon]

Hi @v-xida-msft thank you very much for the information!

Sorry for the confusion. The two lists are being displayed in two different Galleries. The user first selects from the Gallery1 the item related to the List1 "TripsList". The Gallery2 will be filtered using the "identifier" selected in the Gallery1.  In this case, all the items displayed in the Gallery2 will have the same identifier.

Please find below an image from List2, which is being displayed in the Gallery.2 (This is in order for the user to be able to see all the items related to the same identifier at the same time).  Inside the Gallery,2 I have manually include Labels, Drop Downs and Date Pickers which are connected to the SharePoint list. The user can select an assignment within the Gallery and edit and save the information. (Please refer to this link https://powerusers.microsoft.com/t5/Building-Power-Apps/Edit-and-save-only-selected-item-in-Gallery/...)

Due to this, I have not been able to found the OnSucces within the Gallery. Is this still possible using a Gallery?

Thank you!

[v-xida-msft · Accepted solution]

Hi @vsolanon ,

Do you save the changes you made within the Gallery2 Item via clicking the "Edit" button?

If you save the changes you made within the Gallery2 Item via clicking the "Edit" button, please add the following formula within the OnSelect property of the "Edit" button:

Parch(....);  // Update current item in your Gallery2
// Add the following formula
If(
   // Third Rule
   CountRows(Filter('Destinations List', 'Trip Status'.Value = "Postponed" && Identifier = ThisItem.Identifier))>=1 && CountRows(Filter('Destinations List', 'Trip Status'.Value = "In Process" && Identifier = ThisItem.Identifier))=0,
   Patch(
          'TripsList',
          LookUp('TripsList', Identifier = ThisItem.Identifier),
          {
            'Trip Status': {
                              Value: "Postponed"
                           }
          }
   ),
    // Second Rule
    CountRows(Filter('Destinations List', 'Trip Status'.Value = "In Process" && Identifier = ThisItem.Identifier))>=1,
    Patch(
          'TripsList',
          LookUp('TripsList', Identifier = ThisItem.Identifier),
          {
            'Trip Status': {
                              Value: "In Process"
                           }
          }
   ),
   // First Rule
   CountRows(Distinct(Filter('Destinations List', Identifier = ThisItem.Identifier),'Trip Status'.Value))=1,
   Patch(
          'TripsList',
          LookUp('TripsList', Identifier = ThisItem.Identifier),
          {
            'Trip Status': {
                              Value: First(Distinct(Filter('Destinations List', Identifier = ThisItem.Identifier),'Trip Status'.Value)).Result
                           }
          }
   )
);

Please take a try with above formula, then check if the issue is solved.

Best regards,

[vsolanon]

Hi @v-xida-msft it worked like a charm!! thank you so much 🙂

I included this rule under the Save button I have inside my Gallery and noticed it works perfectly, however, I got the message "Delegation warning.  The highlighted part of this formula might not work correctly on large data sets. The "CountRows" operation is not supported by this connector."

-Can this be removed by updating the formula? or which can be the best approach recommended to not affect the TripStatus calculation we just configured?

Also, I included a new validation- Fourth Validation (I figured I missed it when I was testing:) ) and would like to please confirm with you if it was done properly and included in the correct step. I included as the second validation inside the IF function.

Fourth Rule:  If there is at least one item under Destination List (List2) marked as "Completed" and there are no other items in Destination List (List2) marked as "In Process" or "Postponed", the item in List1 should be marked "Completed". This rule must exclude having an item marked as In Process or Postponed in List 2 and might have an item marked as Cancelled.

If(
// Third Rule-

// Fourth Rule---> 1 POST PONED AND NO PENDING (MIGTH HAVE CANCELLED OR COMPLETED)
CountRows(Filter('Destinations List', 'Trip Status'.Value = "Completed" && Identifier = ThisItem.Identifier))>=1 && CountRows(Filter('Destinations List', 'Trip Status'.Value = "In Process" && Identifier = ThisItem.Identifier))=0,
Patch(
          'TripsList',
           LookUp('TripsList', Identifier = ThisItem.Identifier),
           {
                'Trip Status': {
                                         Value: "Completed"
                                     }
           }
       ),

// Second Rule

// First Rule

);

Thank you very much!!

[v-xida-msft]

Hi @vsolanon ,

Have you solved your problem?

Is the solution I provided above helpful in your scenario?

If you have solved your problem, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

Best regards,

[vsolanon]

Hi @v-xida-msft ,

I would like to please confirm with you the if it is possible to remove the Delegation Warning message that appears after I incldue the rules in the Save Button and to validate if the below rule (Fourth rule) was created correctly 🙂

Message: "Delegation warning.  The highlighted part of this formula might not work correctly on large data sets. The "CountRows" operation is not supported by this connector."

Fourth Rule:

Thank you so much!

[v-xida-msft · Accepted solution]

Hi @vsolanon ,

Do you want to remove the Delegation warning issue from your formula?

Firstly, the Delegation warning issue is not an error, it just means that you could not delegate the data process to your data source itself, instead, you could only process data locally within your app.

In default, you could only process 500 records locally within your app, you could consider change the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app, then you could process 2000 records locally at most in your app.

Currently, the CountRows function is not a delegable function in SP List data source, so your Delegation issue is related to this function. More details about delegable functions supported in SharePoint, please check the following article:

https://docs.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-ope...

If the amount of your data source records ('Destinations List') is not more than 2000, you could ignore this warning issue. If the amount of your data source records is more than 2000, you could consider bulk-load your data source records into collection in your app, then use the collection as data source in your app instead of the original 'Destinations List'.

Please check and see if the following solution could help in your scenario:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Loading-60-000-SharePoint-List-Items/m-p/448...

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

https://powerusers.microsoft.com/t5/Building-Power-Apps/Sort-gallery-with-multiple-fields/td-p/37973...

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/Delegation-Formula-Help/m-p/394039#...

Best regards,