Client Matter Lookup

[spadminmh]

We have several forms where we would like individuals to choose a specific client/matter to input in a form.  I have this connected to an azure SQL database which has two tables, Client and Matter.  The tables are very simple.  The client table contains a primary key , CliCode and CliName.  The Matter table contains a primary key, Mat Code, MatName and MatCliNbr which links each matter to a single client in the Client Table.

 

As of Now, I can search for a Client Number and then Choose the Client Description that corresponds to the Client Number but I can't search the Client Description Field itself. 

Once a client Number is entered, I can search through the only Matter Numbers that correspond to that client which is correct.  Ideally, I'd like to be able to search through the Client Description or Client Number and have it populate both Client Number and Matter Number.  

I'd also like to be able to search for a Matter Number or Matter description and have it populate the Client as a Matter can only belong to one Client but I realize this may be too complicated.  

Right now I have the Client Description (DataCardValue3) OnChange value set to Set(SelectedValue, First(Filter('[dbo].[Client]', CliCode = DataCardValue2.Selected.CliCode)))

The Item Value for the Client Description (DataCardValue3) is set to Filter('[dbo].[Client]', CliCode = DataCardValue2.Selected.CliCode)

Am I not able to search Client Description because it is dependent on the CliCode value?  What is a better way to express this?  

[Mr-Dang-MSFT]

@spadminmh, from your description, it sounds like each dropdown and its Items are dependent on every other control. Normally if you try to point one control to another control and back again, this would cause a circular logic and not work out.

That said, if you wanted to simulate this anyway, I think you're on the right track with using collections.

Here's the sequence that I'm imagining:

1. Upon clicking a dropdown (OnSelect), ClearCollect the items you want the selected dropdown to have. (It would start off empty).
2. Upon changing the selection of the dropdown (OnChange), ClearCollect the Items that other controls would have.
3. Point each dropdown to its respective collection.

Note: if you want a default value for each one, then you can use variables for them in sequence with the collection.

Let me know if this made sense.

[spadminmh]

It does and thank you for your response.  I left out the part where I was using ComboBoxes instead of DropDowns.  Sorry to switch gears but I like the idea of using a single field to search for either client number or client description so I changed my form quite a bit.  I have just about everything working correctly with two exceptions...

1)  I can search for client number or client name, but only the client number is populated once it has been selected...

2) Since a Matter can only belong to one client, I'd like to be able to search in the Matter field first and have it update the Client field appropriately, however the Matter Field will not search for any data until the Client field has been populated.  

[Mr-Dang-MSFT · Accepted solution]

@spadminmh, 

I can clarify a few things about comboboxes and dropdowns here. 

The selection in a dropdown outputs the value in one column. So that is a single value of a single data type: numeric, text, boolean, etc.

The combobox outputs a full record when selected. Onlyl 19299 appears in the combobox for brevity, but indeed the full record is chosen.

You can reference the selection in the combobox in two ways. Disable or enable multiple selection in the combobox depending on your needs:

• the most recent individual selection: ComboBox.Selected.[optional field name here]
• a table of all of the selections: ComboBox.SelectedItems

Depending on the data type you want to work with, your filter will change. Below is an example of filtering a table of Matters whose ClientId matches the Client Id selected in the combobox.

Filter(Matters, ClientId = ComboBoxClients.Selected.Id)

Let me know what your filter ends up looking like.