Powerapps: Selecting a record for a form using multiple criteria

[schneice]

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

[Drrickryp]

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.  

[schneice]

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.

[Drrickryp · Accepted solution]

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)

[schneice]

Drrickryp - That was money!  Thanks