Combo Box Help with SQL 2 tables

[ckelusak]

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.

Diagram

XRef_ScopeOfWork

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

[StalinPonnusamy]

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}
              }

[ckelusak]

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.

Thoughts? 

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

~Chris

[StalinPonnusamy]

Hi @ckelusak

DefaultSelectedItems property 

{Value:ThisItem.'LookupColumname'.Id}

My Sample

[StalinPonnusamy · Accepted solution]

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
    )
);

• 
• DefaultSelectedItems property

DefaultScopeOfWork.ScopeOfWorkName

•