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

Powerapps: Selecting a record for a form using multiple criteria

Hi,

 

I am trying to use a form to edit data from a table in my OneDrive.

 

The table has 2 column that together act as a unique record keys: 1) the asset name 2) the date.  The rest of the columns have observation data on the asset for a specific date.

 

My form is always blank because I can't seem to select just one record.  It's not clear to me what I should put as the "Data Source" vs "Item".

 

I have had luck indentifying the unique record in a Data table

 

First(Sort(Filter(Asset_Data, Gallery1.Selected.Asset = Asset),Date,Descending)) .... where Gallery1.Selected.Asset is the result of the user selecting the Asset from a gallery list.

 

But I can't seem to translate this code that works in a Data Table into what I need to identify a record for the Form.

 

Any thoughts?  Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Powerapps: Selecting a record for a form using multiple criteria

Hi @schneice,

 

I think i get what you are doing.  Could it work in two steps? 

  1. On a blank screen, add  a dropdown with an Items property of Distinct(Asset_Data, Asset). // this would create a 1 column collection consisting of a Results column that will be the filter criteria for your Gallery or Datatable.  
  2. Add DataTable with its datasource as Sort(Filter(Asset data,Assets=Dropdown1.Selected.Result), Date,Descending). // This will have a Date field as well as any other fields you want to show, sorted by the latest date in the table.  (You could use a Gallery instead of a Datatable in this step)  
  3. Add an Edit form with a datasource as Asset_Data and the Item as DataTable1.Selected.
  4. If you wanted to filter the Datatable further, lets say for a range or items since a particular date, you could add a Datepicker and modify the Items property in the datatable as follows: Sort(Filter(Asset data,Assets=Dropdown1.Selected.Result && Date > Datepicker1.SelectedDate), Date,Descending)

 

View solution in original post

4 REPLIES 4
Highlighted
Super User III
Super User III

Re: Powerapps: Selecting a record for a form using multiple criteria

Hi @schneice,

 

It may be easier than you think if your data source for the Gallery is the same as the data source for your Edit form.  If so, then the Item property is should be: Gallery1.Selected.  If not, let me know.  A couple of other suggestions.

 

  • First(Sort(Filter(blah.blah..)) can be reduced to Lookup(Sort( ).  That always finds the first instance of a string in a column. You can Lookup from the bottom of your table by Sorting the table by Date, Descending as you did in your example and wrapping the result in the Lookup().
  • Instead of using two columns as a primary key, It may be better to just add an ID column to your data source and increment it automatically as you add new items to your table rather than than to concatenate the two columns. This can be done by putting the Default property of the ID field as If(IsBlank(Parent.Default),Last(datasource).ID+1,Parent.Default), in your Edit form and either hide it or make it View only. That way it will only increment the table if the FormMode is New.
  • The primary key of a table should never be editable by a user. Also, it will be easier to use a simple ID column as your primary key as you build up your app and need to create relationships with other tables.   
  • I played around with trying to concatenate a text string and a date and I couldn't get it to work. (That doesn't mean that it can't be done, only that I couldn't  figure out how to do it). In Powerapps, there is a Concatenate function but it will only work with text strings and errors out when i tried combine text and a date.
  • Excel tables are ok for simple apps but if you want to get more sophisticated and add more capabilities, you should consider using a more robust platform for your data source such as CDS, Sql, or Sharepoint.   

I hope this helps.  

Highlighted
Frequent Visitor

Re: Powerapps: Selecting a record for a form using multiple criteria

Thanks!

 

They are from the same source.  The issue with the Gallary1.Selected, is that it selects only one of the parameters - the asset.  No need for the user to see the asset listed once for every day as they are drilling down.

 

It is not clear to me how a combined field would help here.  I still need to filter the dataset for the asset and then pick the most recent record.   The query to get the most recent record would still have to be on a second field, and it feels cleaner to have it on a date than asset+date.

 

It seems like there should be a way to use first or lookup to identify the unique record for the form.  It works for Data table.  But maybe you can't.

Highlighted
Super User III
Super User III

Re: Powerapps: Selecting a record for a form using multiple criteria

Hi @schneice,

 

I think i get what you are doing.  Could it work in two steps? 

  1. On a blank screen, add  a dropdown with an Items property of Distinct(Asset_Data, Asset). // this would create a 1 column collection consisting of a Results column that will be the filter criteria for your Gallery or Datatable.  
  2. Add DataTable with its datasource as Sort(Filter(Asset data,Assets=Dropdown1.Selected.Result), Date,Descending). // This will have a Date field as well as any other fields you want to show, sorted by the latest date in the table.  (You could use a Gallery instead of a Datatable in this step)  
  3. Add an Edit form with a datasource as Asset_Data and the Item as DataTable1.Selected.
  4. If you wanted to filter the Datatable further, lets say for a range or items since a particular date, you could add a Datepicker and modify the Items property in the datatable as follows: Sort(Filter(Asset data,Assets=Dropdown1.Selected.Result && Date > Datepicker1.SelectedDate), Date,Descending)

 

View solution in original post

Highlighted
Frequent Visitor

Re: Powerapps: Selecting a record for a form using multiple criteria

Drrickryp - That was money!  Thanks

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,828)