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

Client Matter Lookup

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.

 

 

 CM.JPG

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?  

 

1 ACCEPTED SOLUTION

Accepted Solutions

@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.

View solution in original post

3 REPLIES 3
Mr-Dang-MSFT
Power Apps
Power Apps

@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.

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

 

 

smith.JPG

smith2.JPG

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.  

 

@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.

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

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

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors
Users online (2,490)