Answered

Multiple Data Sources and Cascading Drop downs

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello and happy 4th of July,

I'm building a canvas app that is a series of forms that draw data from multiple SharePoint Lists. One list consists of a set of data from MS Project, and the Gallery that initiates the form entry process consists of the Project Names and Project Owners as Title and SubTitle for the Gallery. There is a button as part of the Gallery that takes the user either to a form to enter New Project Status info or to a form to Edit Existing Project Status info.

There is a second SharePoint List that contains information supplied by the Edit or the New Project Status Forms. If there is already Project Status information in the Status list the button navigates to the Edit Form and carries the project name over into the Edit form via the formula shown at the top of the screen shot here:

All works well at this point. But the cards below project name all have drop downs that have Choices that can be selected. But I want the default value for each card to be the selection that already exists for that field within the 'Project Status' SharePoint list. I can't seem to hit on the proper formula to pull this default information into the form. Can anyone help?

Additionally, not shown directly above but below the three drop down cards are multi-line text entry card values, and the same issue exists for these cards as well. Not sure if there is a different solution for Text entry as for drop downs.

Any help would be most appreciated, as I am in a bind at work with this and have spent hours trying to figure it out.

Craig

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

BenFetters on at

Like (0)

Report
Copy link

Link copied!

Hi Craig,

Happy Fourth to you as well! I've been there, not super fun trying to figure things out for hours and hours. That seems like an pretty cool app you're making though. Let me see if I can help as I've done something extremely similar to this recently.

If I understand correctly, you're needing to being in information from the Project into the form that is in "edit" mode, or is the form in "New" mode then? One idea would be to have the information you're entering be a part of the project, so really you would be editing the project information, which would show the latest information that was entered, and then once you've submitted or saved that information you could us the Patch function to create a new item in the 'Project Status' list that would capture all that information they just saved back to the project. Did that make sense at all? And maybe that doesn't work for you're situation.

If it doesn't here's how you can bring in that information.

For the drop downs, try using a formula like this in the "Default" or "DefaultSelectedItems" property of your dropdowns (depending on if you can select multiple items or not, Default being for one item, DefaultSelectedItems being for multiple items of course): ProjectGallery.Selected.PreviousMonthStatus

Of course, the gallery and field name might be different, but the logic should work if I understand the situation correctly. You might have to try putting this on either the Default or DefaultSelectedItems property of the drop downs and Include an "if statement" if you're using this form in different scenarios where you wouldn't want the default being filled in this way.

Another idea is to create a variable for the project item or item that is going to be used to fill in the drop downs and multi-text fields. When they click on that button in the gallery, if could have something like: Set(varProjectRecord,ThisItem)

Hopefully that will allow you to set the default values of the drop downs and text fields. The text field would look something like: varProjectRecord.MultiTextFieldName

If any of this doesn't make sense or didn't work just let me know and I'd be glad to respond.

If it did work, feel free to mark this as the "Solution" so that others can find it. 🙂

Kind regards,

Ben Fetters
www.sovereignsp.com

Was this reply helpful? Yes No
BenFetters on at

Like (0)

Report
Copy link

Link copied!

Or, if the form is in edit mode and assuming there's only ever one project status that you're editing, could you just have the item set to: Filter('IBP Project Status','Project Name' = BrowseGalleryStatus.Selected.ProjectName)

Maybe I'm not understanding the situation all the way.

But, for the drop downs/text inputs in the Default or DefaultSelectedItems properties, you should be able to put Filter('IBP Project Status','Project Name' = BrowseGalleryStatus.Selected.ProjectName).FieldName into the formula bar and that should work, of course changing "FieldName" to the right field name that you need.

Does that work?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Ben,

Thank you so much for the time you obviously spent thinking about my issue and responding. I know what you are suggesting and I believe I tried that among several other things. Here is the issue (I think).
Filter('IBP Project Status','Project Name' = BrowseGalleryStatus.Selected.ProjectName) is exactly the formula that I am using to bring the Project Name over from the Gallery to the Edit Form. What this is doing, I believe, is going to the 'IBP Project Status' list and filtering it down to the one project that equals the Project Name from the MS Project list (what is in the Gallery). Wonderful, that is what I want it to do. That works because if there is a matching Project Name it means there is material in Project Status to edit. BUT, I can't use that same functionality to pull in 'Previous Month Status', 'Scope Status', or 'Cost Status', because those fields are not in the MS Project SP list, just the Project Status list. So, what I am needing is some way to use the 'ProjectName' that is brought in to the Edit Status form from the Project Status list to bring in the selections associated with that ProjectName for the three drop downs, also from the Project Status list. Seems like this should be easy, and it seems like Shane Young has done exactly this in his cascading drop down video. The difference being, I think, that I am trying to use that functionality only for the default, not as a means of filtering down the selection options. Does this make sense?

And yes, for any given project from MS Project and in the Project SP list, there is only either one or no entries in the Project Status list. If there is no entry the app navigates to 'Enter New Status Info' form (with the carried over ProjectName) and if there is already an entry, it navigates to 'Edit Project Status' form (with the carried over ProjectName), where all the info that exists in the current entry for the project in the Project Status SP list is shown as the defaults for the edit form.

Of course, upon submit for either form it needs to write the info back to the Project Status SP list.

I have not used Patch or Variables for apps yet. In fact this is really my first app. I'm thinking Variables might be the way to go, but I am not sure. I hope I have clarified the issue as opposed to obfuscating it. Thanks again for your help.

Craig

Was this reply helpful? Yes No
Verified answer

BenFetters on at

Like (0)

Report
Copy link

Link copied!

You bet, it's nice to be of some sort of help. And that actually made things a lot more clear so thank you for explaining that. What you can try doing then, is in the DefaultSelectedItems property (you can try the Default property as well if that one doesn't work) of any of the drop downs, try putting something like: First(Filter('IBP Project Status','Project Name' = BrowseGalleryStatus.Selected.ProjectName)).'Previous Month Status'

That should bring that right values over, I believe. The "First()" part of that formula is just getting the first item that meets that filter. Of course there's always going to just be 1 item at the most in the list, but Power Apps doesn't know that so you have to just have it get the first item that meets that filter. Does that make sense? Let me know if that does the trick.

I know you can do what it is you're trying to do, we just have to figure it out. You can also screen shot any of the formula's you try and what the error message is if there is any.

Also, just to make sure, 'IBP Project Status' is the list that contains the Previous Month Status, Scope Status, and Cost Status correct?

By the way, from your screen shots and just the complexity of this app it it seems like it's a great first app!

Was this reply helpful? Yes No
balakowsalya 15 on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous & @BenFetters,

Hope you are doing well!
I'm working on a similar kind of use case that you have mentioned here in this thread. I'm trying to source data from multiple SharePoint list in the same site. Same as your case, I will have 2 screens in the PowerApp where the first screen will list down all the records from one list(say master data containing only 'ID' and 'Name') and once user selects a particular item from the gallery view, it should take them to the second screen and detailed Info related to that particular item should be available to view/edit in the second screen.

Basically, one list for the master data and the rest of the list will have detailed info related to particular ID in master list.

Hope the scenario is clear and understandable. Please let me know any additionally inputs are needed. It will be really helpful if you can guide me in this area.

Thanks,
Bala

Was this reply helpful? Yes No