cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tomgardzinski
Helper IV
Helper IV

Creating / Populating a sharepoint list from two other sharepoint lists using DropDowns

Hello Community,

I'm stuck with a problem. Basically I have two SP lists with data, and I require a 3rd SPL to be created and populated based on the available information in Auditors SPL and Audit Type SPL.

Auditors SPL has a column AuditorSPL with a list of auditors (person field), and in AuditTypesSPL there is one column  (Title column, single line text).

In PowerApps, I have created a screen with a New Form called Schedule Audit and its data source is the 3rd SPL called Audits.

In the Audits SPL I have several columns, pertinent columns to this issue are: Auditor, Auditor Email, Audit Type.

In PowerApps, in the form, the Manager is meant to select the type of audit and then an Auditor to complete the Audit.

In the DataCard for Auditor, I created DDAuditor with Items set to SPL 1 and Auditor column : Choices([@IPAC_Auditor_List].AuditorSPL). No error displayed when I created this, which was promising.

From this, I could set the Auditor Data Card Value to DDAuditor.Selected.DisplayName and the AuditorEmail DCV to DDAuditor.Selected.Email.

My first problem is that nothing appears in DDAuditor even though I have at least one record in the SPL.

My second problem is that in the Audit Type DataCard, which I also created DDAuditType. I reference the AuditType SPL and column as: Choices([@IPAC_AuditType_List].Title), but I receive an error message stating "The function Choices has some invalid arguments".

 

Any help on this matter would be greatly appreciated.

Thanks,

Tom

1 ACCEPTED SOLUTION

Accepted Solutions

@v-yutliu-msft @poweractivate 

Yes! This is the solution. Galleries are your friends in this manner.

I've created two galleries, one with audit types the other with auditors. Then I set the datacardvalues to gallery.selected.<name> and it populates. I just need to figure out some form control so that user can edit nicely.

Thanks for your help and steering me in the right direction which forced me to ask questions of my methods.

View solution in original post

5 REPLIES 5
poweractivate
Community Champion
Community Champion

@tomgardzinski 

 

 

  1. What is your DefaultMode Property (Form Mode) of the form?

     

    If it is set to FormMode.New then it is not usually intended to be used for editing existing items but for submitting brand new items.

     

    Try changing it to FormMode.Edit or FormMode.View (if applicable).


  2. About Choices, you must reference the actual Column by Lookup and not by the string text value.

    https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-choices

    Syntax

    Choices( column-reference ) [emphasis added]

    • column-reference – Required. A lookup column of a data source. Don't enclose the column name in double quotes. The reference must be directly to the column of the data source and not pass through a function or a control.

 

 

 

 

 

 

 

 

 

 

 

v-yutliu-msft
Community Support
Community Support

Hi @tomgardzinski ,

Could you tell me the data type of Auditor and  AuditorEmail ,Audit Type in Audits list?

I assume that they are all text type.

Firstly, you need to connect the app with the three lists.

1)Please set Auditor combo box's Items:

Choices(IPAC_Auditor_List.AuditorSPL)

set the   Auditor's datacard Update:

DDAuditor.Selected.DisplayName

set the   AuditorEmail 's datacard Update: 

DDAuditor.Selected.Email

 2)Please set DDAuditType combo box's Items:

Distinct(IPAC_AuditType_List,Title)

set DDAuditType data card's Update:

DDAuditType.Selected.Result

//you could only use choices function for choice type, lookup type or person type.

For text type, I suggest you try Distinct function. Distinct function will return a table with a column named Result.

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello thank you kindly for your response. Unfortunately it has not fixed the 2 issues I'm faced with.

1. You are correct in assuming that Auditor, Auditor Email, and Audit Type in the Audits List are all single line text data types. This is I think the best way to try and pass a value into a sharepoint list from other lists (but I could be wrong).

2. After making the updates as you suggested I still have the issues which are both DDAuditType and DDAuditor are blank, even though there are records in the SPLs I am attempting to pull from.

 

Below is my form which is set to DefaultMode FormMode.Edit and Datasource IPAC_Audits. I've provided the rest of the details of each field below the image.

Using 2 SPL to create a 3rd.PNG

DataCard1

    Update = DDAuditType.Selected.Result

            Title

            Default = DDAuditType.Selected.Result

            DDAuditType

            Items =  Distinct(IPAC_AuditType_List,Title)

DataCard2

  Update = DDAuditor.Selected.DisplayName

             AuditorName

             Default = DDAuditor.Selected.DisplayName

             DDAuditor

             Items = Choices(IPAC_Auditor_List.AuditorSPL)

DataCard3

   Update = DDAuditor.Selected.Email

             AuditorEmail

             Default = DDAuditor.SelectedText.Email

 

Have you tried replicating my instance on your end? Perhaps you could try creating two SPLs, then in PowerApps using an Editform populate into a 3rd SPL data which is obtained from SPL1 and SPL2.

 

Thank you for your time.

@poweractivate @v-yutliu-msft 

I'm currently playing with the idea where my two SPLs; 1. AuditType and 2. Auditors will be galleries on a screen, with the form being set beside the galleries. Selecting an item in a gallery would in theory populate the form. Thoughts on this matter?

@v-yutliu-msft @poweractivate 

Yes! This is the solution. Galleries are your friends in this manner.

I've created two galleries, one with audit types the other with auditors. Then I set the datacardvalues to gallery.selected.<name> and it populates. I just need to figure out some form control so that user can edit nicely.

Thanks for your help and steering me in the right direction which forced me to ask questions of my methods.

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,289)