cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ckelusak
Frequent Visitor

Combo Box Help with SQL 2 tables

Hello!

 

Been trying to get this to work and looking through posts I think I am close but could use a push over the finish line. I figured out how to write with the combobox but can't figure out reading from the DB to populate the items correctly. 

 

DB is setup in SQL, I have (for this individual ComboBox) 3 tables that work together, "ProjectData", "xref_ScopeOfWork" and LU_ProjectData_ScopeOfWork. The Xref table holds the values for the ComboBox, with the main identifier for the project coming from ProjectData and the text values from the LU_ProjectData_ScopeOfWork, which is a Lookup table.

ckelusak_0-1627505821037.png

Diagram

ckelusak_1-1627506017616.png

XRef_ScopeOfWork

ckelusak_2-1627506061677.png

 

LU_ProjectData_ScopeofWork

 

 

What I am trying to get to is when someone clicks on an item in the Gallery a new page appears, which has the relevant data, I got everything but the ComboBox working where I can't get it to show the items that are currently stored in the DB.

 

Currently I have my Items set to LU_ProjectData_ScopeOfWork, which correctly shows the list of items when I click on the DropDown so I can select one.

 

I tried a few things, and think I am close with DefaultSelectedItems needing to be a Filter of the table, but get stuck there. Not sure if it should be DefaultSelectedItems  or Default  as each Project will have its own specific entries there.

 

Any Suggestions would be great!!

 

~Chris

 

1 ACCEPTED SOLUTION

Accepted Solutions
StalinPonnusamy
Community Champion
Community Champion

Since those are N:N and multi select combo box, get those values ahead (Onscreen visible) and assign the collection to the Combo box

 

  • OnVisible property on the EditScreen

 

Clear(DefaultScopeOfWork);
ClearCollect(
    ColScopeOfWorks,
    Filter(
        ProjectScopeOfWork,
        ProjectID = BrowseGallery1.Selected.ProjectDataID
    ).ScopeOfWorkID
);
ForAll(
    RenameColumns(ColScopeOfWorks,"ScopeOfWorkID","ScopeOfWorkID1"),
    Collect(
        DefaultScopeOfWork,
        Filter(
            ScopeOfWork,
            ScopeOfWorkID = ScopeOfWorkID1
        ).ScopeOfWorkName
    )
);

 

  • StalinPonnusamy_0-1627782415318.png

     

     

  • DefaultSelectedItems property

 

DefaultScopeOfWork.ScopeOfWorkName

 

  • StalinPonnusamy_1-1627782415270.png

     

     

View solution in original post

4 REPLIES 4
StalinPonnusamy
Community Champion
Community Champion

Hi @ckelusak 

 

It should be DefaultSelectedItems which will be filtered for the SP list and Items that has full SP list (no filter) but specific field selected.

 

And when we save we should use lookup

{Lookup:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                        Id:Combobox.Selected.ID,
                        Value:Combobox.Selected.Text}
              }

Hi @StalinPonnusamy ,

 

Thanks for your response, saving isn't currently an issue, I have that working just fine with my ForAll statement, which I can share here as well.  Where basically I am removing all records that have to do with the item selected and then writing all the selected ones for that ComboBox. I am doing it that way to make sure there are no left overs, as in someone unselects one. This code is being kicked off with a button after they would make their selections in the ComboBox, which updates a collection that I have called "ScopeOfWorkCollection". 

RemoveIf(
    '[dbo].[XREF_ScopeOfWork]',
    ProjectDataID = projectItem.ProjectDataID
);

ForAll(
    ScopeOfWorkCollection,
    Patch(
        '[dbo].[XREF_ScopeOfWork]',
        Defaults('[dbo].[XREF_ScopeOfWork]'),
        {
            ProjectDataID: projectItem.ProjectDataID,
            LUScopeOfWorkID: ScopeOfWorkCollection[@LUScopeOfWorkID]
        }
    )
)

 

The issue is getting the values to read from the SQL DB when they first navigate onto this page, so they can see what was already selected.

 

I have my Items set correctly already, pointing to the LU_ProjectData_ScopeOfWork table, and can verify I see them in the DropDown. 

 

I have a clearcollect on OnChange to set my collection whenever modified.

 

But the DefaultSelectedItems seems to be my problem child, as I am trying to get all the values stored, so I am using the Filter('[dbo].[XREF_ScopeOfWork]',ProjectDataID=projectItem.ProjectDataID) to return a table, but my ComboBox stays blank rather then showing them selected.

ckelusak_0-1627596100817.png

 

Thoughts? 

 

Also, thank for responding and thanks in advance for any future responses!

 

~Chris

StalinPonnusamy
Community Champion
Community Champion

Hi @ckelusak

DefaultSelectedItems property 

 

{Value:ThisItem.'LookupColumname'.Id}

 

My Sample

StalinPonnusamy_0-1627607513399.png

 

StalinPonnusamy
Community Champion
Community Champion

Since those are N:N and multi select combo box, get those values ahead (Onscreen visible) and assign the collection to the Combo box

 

  • OnVisible property on the EditScreen

 

Clear(DefaultScopeOfWork);
ClearCollect(
    ColScopeOfWorks,
    Filter(
        ProjectScopeOfWork,
        ProjectID = BrowseGallery1.Selected.ProjectDataID
    ).ScopeOfWorkID
);
ForAll(
    RenameColumns(ColScopeOfWorks,"ScopeOfWorkID","ScopeOfWorkID1"),
    Collect(
        DefaultScopeOfWork,
        Filter(
            ScopeOfWork,
            ScopeOfWorkID = ScopeOfWorkID1
        ).ScopeOfWorkName
    )
);

 

  • StalinPonnusamy_0-1627782415318.png

     

     

  • DefaultSelectedItems property

 

DefaultScopeOfWork.ScopeOfWorkName

 

  • StalinPonnusamy_1-1627782415270.png

     

     

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,097)