cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Power Apps
Power Apps

Re: Client Matter Lookup

@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
Highlighted
Power Apps
Power Apps

Re: Client Matter Lookup

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

Highlighted
Frequent Visitor

Re: Client Matter Lookup

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.  

 

Highlighted
Power Apps
Power Apps

Re: Client Matter Lookup

@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
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,812)