Cascading dropdowns...lookups... and patch from 2 lists into 1

[Kadd]

Hello !!

Hope you are all doing great.

Ok... I have a new issue that i'm struggling with.

I have a SP List "DeptProcAct" with columns Department / Process

I also have a second list "Dept" with columns DPADept / DPAProc.

DPADept is a lookup column of Department and DPAProc is a lookup column of process.

I created a Powerapps form in List "Dept" where i have my fields. Since those are 2 dropdowns, I made them cascading.

In the Items property of "DPADept" i have "Distinct(DeptProcAct,Department)"

and in the items property of DPAProc I have this "Distinct(Filter(DeptProcAct,Department=DataCardValue48.Selected.Result),Process)"

Where DataCardValue48 is the data card of DPADept.

Cascading seems to be working.... if i select a department the process box shows the corresponding values

* The first problem is when i try to save my fields using a save icon with formula SubmitForm(Form1) it doesn't save anything.

* When i use a Patch function, 

Patch(Dept,{DPADept:DataCardValue48.Selected.Result,DPAProc:DataCardValue49.Selected.Result}) ,

I have an error. it says DPAXXX does not match the type record. found type text

* but i also have a some itnitialization value issue cause my boxes seem to load the last used values and not a blank one (when the form is empty)

I can imagine it's not really clear. I tried lots of things including something with "@odata..." but till now nothing worked properly so i'm doing something wrong...

I had another idea to create 2 dropdowns in my Dept list and use the distinct values of the columns, make them cascading but i had another issue with this, i guess it was not saving the values in the list or they wouldn't load back when i reopen the form... honestly, i tried so many things that i don't even remember 😞

Would one of you be able to help me on this ??

Thanks a lot in advance !!

Regrds 

it saves the saves the value of Dept but the value of Proc stays blank 

[WarrenBelz]

HI @Kadd ,

Your needs are a little unclear.
I am assuming there will be only one record where your Department equals the selected result? Probably not but if so the below will update all matching records

UpdateIf(
   Dept,
   DPADept=DataCardValue48.Selected.Result,
   {DPAProc:DataCardValue49.Selected.Result}
)

More likely are you trying to update the current record with the result selected in DataCardValue49 (the Process). Then you would do this

UpdateIf(
   Dept,
   ID=ThisItem.ID,
   {DPAProc:DataCardValue49.Selected.Result}
)

You have also not said what type of field DPAProc is in the target list. If it is a Choice field, you can just put this in the Update of the DataCard and SubmitForm will work

{Value:DataCardValue49.Selected.Result}

If you can tell me exactly what you are trying to update and with what, this is not a difficult exercise

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[WarrenBelz]

Hi @Kadd ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Kadd]

Hi @WarrenBelz ,

I'm really sorry for the very late reply, i've been a bit overloaded and missed your answer.

First, thanks a lot for it.

I didn't have the opportunity to try your solution yet but... I "solved" my problem another way.

I figured out this first screen  (Dept) seems to be the only screen where I have this issue ...at the moment... but i also figured out that on this screen, I don't need to do any selection because my "Dept" list is prepopulated with all the values i might need. I never create anything there inside. so i don't need to do a selection.

Now, i'm sure this will come back at some point so i will definitely try your solution to be sure it helps. 

I 'm very busy with this application i'm trying to set up. I have to present it somewhere next week. I'm working on different features and am stuck on something else again...but after the presentation, I'll have 1 or 2 days more relaxed where i'll be able to test the few things i kept aside.

Thanks again for your reply,

Regards 

[Kadd · Accepted solution]

Hi @WarrenBelz ,

Sorry for the very long delay. 

I finally could come back to this.

I had the same issue for a new screen i created and had more time to play around.

Still based on my DeptProcAct List  (Department - Process - Activity) but now working with my Dependencies list.

I created 3 dropdowns that i made cascading (I could do it in the first part of my post so this was not really the problem).

BUT ... I finally found the way to do the patch formula. since i'm using lookup fields for my Department, Process and Activity ... these being liked to my Department, Process and Activity fields in DeptProcAct, I had to do this :

Patch(
            Dependency,
            {
                DeptTargeted: {
                    '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                    Id: Value(varDPAID),
                    Value: Dropdown15.Selected.Result
                },
                ProcTargeted: {
                    '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                    Id: Value(varDPAID),
                    Value: Dropdown16.Selected.Result
                },
                ActTargeted: {
                    '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                    Id: Value(varDPAID),
                    Value: Dropdown17.Selected.Result
                }
            }
        );

Voila ! 🙂

Thanks a lot again for your help !!

Regards

[BailSpin84]

Hi Kadd, 

Man I am glad you got this. I am stuck on the Cascading Drop-Down Lookup Patch, lol, now myself and can't figure out what you did to get this.  

/*******Use Patch to add a new item*******/

Patch(WFP_MainList_3, Defaults(WFP_MainList_3),
{

/*******Single line of text columns*******/
existingNewPD:txt_Order_PD.Text,
FY_1_1: lbl_Order_Year_FY1.Text,

/*******Look Up columns*******/
officialPositionTitle:{
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: Value(varID),
Value:drp_Order_Title.Selected.Result
},

/*******Number columns*******/
FY_1_Num:Value(txt_Order_amount_FY1.Text),
FY_1_Cost:Value(lbl_Order_numCost_FY1.Text),
estCost: Value(lbl_totalFY_num.Text),
a_ecosystem: Value(txt_Order_A.Text)

});

Where did you set your variable to get the record of the ID? Did you not have to use Defaults in your statement to add a new item? Any help would be appreciated & I will probably need to explain my criteria and intention better. Thank you!

[Kadd]

Hello @BailSpin84 ,

Oh la la ... that's 2 years ago...

the context of my app was, I created a big app using a SharePoint site as "a databse". I had around 20 referential lists and 15 data lists all connected to my powerapp. I was working with departments. processes and activities ...these were initially created in a reference list called DeptProcAct (that i talk about in my 1st post)...kind of big database containing all the departments / processes / activities.

In my case, I needed to have all the items linked together throughout my app so when i open my form, each screen has a filter that displays the information related to the selected department/process only. To do this, I used an additional ID (column that i created in DeptProcAct and that I reuse in all my other data lists as a primary key) ...is it clear till now ?

so in DeptProcAct, i have :

ID      Department        Process        Activity     DPAID

1           D1                      P1                 -               1

2            D1                     P1                  A1            1

3            D1                     P1                  A2            1

Where DPAID is that fictive ID that I use in all my lists and is in fact the ID of the item corresponding to the group Department/Process (since in all my app the display is ruled by the group department/process) in DeptProcAct where activity equals " - "

At the end, on each screen i filter the list based on this DPAID... that I put in a variable called varID....

This is where my ID comes from and how i use it ! Doing this, i solve the duplicates issue, the formulas are lighter and more efficient cause items are uniquely identified i use this in my workflows as well ....i don't need to filter on the department...process...activity...that can very often be duplicated.

voila ! that's the idea  (there are other things but it's not relevent here...)

So now since you have this additional ID (DPAID = varID) knowing that dropdowns are complex components with formulas requiring a complex structure... you know where the ID comes from and ...you have your value

Hope it helps 🙂

[BailSpin84]

Thank you so much @Kadd! I got the jist of your approach and will look to something similar. I appreciate your thoughtful response. 

[Kadd]

Hello @BailSpin84 ,

you're welcome !

If my approach helps you...i'm happy for you 🙂