Search SharePoint List and Display last value entered

[masterp]

Hello Everyone,

Sorry, I'm new to development and PowerApps and have a question for the community. 

I'm currently, working on an app for our company in which form entries are automatically collected and stored in a Sharepoint list.  Each entry has a unique identifier column and for any new entry we would like to collect the previous entry for that unique identifier.

For Example:

User1@contoso.com submits an entry for vehicle VIN (Unique Identifier) and then opens the form later on to submit another entry for that same VIN.

User2@contoso.com  submits an entry for vehicle VIN (Unique Identifier) and then opens the form once again to submit another entry for the same VIN.

Date	Email	VIN (Unique Identifier)	Starting Mileage
08/03/20	user1@contoso.com	XXXX123	45,000
08/03/20	user2@contoso.com	XXXX432	58,000
08/04/20	user1@contoso.com	XXXX123	45,050
08/04/20	user2@contoso.com	XXXX432	68,200

Is it possible to for PowerApps Form entry to automatically pull the previous information for that VIN (Unique Identifier).

For example:

New Form Entry:

Date	Blank
Email	Blank
Vehicle VIN (Unique Identifier)	Select
Starting Mileage:	If the above VIN is selected, look at the last entry submitted for that VIN and display value here.

Is this possible?

Thank you,

[RandyHayes]

@masterp 

First, when you say "Form" - are you actually using an EditForm?  If so, then you will have this ability built in.

If this is a custom form, then you will need to provide that functionality yourself.

Usually, you will have some sort of Gallery, DropDown, Table, etc. that you are selecting the vehicle from.  Let's say in this case it is Gallery1.

For your fields on your form then you will be able to use these formulas on the Default properties of your text input controls (This one for Starting Mileage):

First(
    SortByColumns(
        Filter(yourDataSource, VIN=Gallery1.Selected.VIN),
        "Date", Descending
    )
).'Starting Mileage'

This will filter the data by the unique VIN and then sort by the dates and then take the First (in this case sorting descending, so the first it the last) and then use the property associated with it.

I hope this is helpful for you.

[masterp]

@RandyHayes 

Yes, I'm using EdithForm and not a custom form and I have a dropdown DataCardValue.  Well hope that makes sense. 🙂

Also, thanks for the quick reply but unfortunately my code is not working. Below are my values:

The Table (SharepointList) containing date is = Vehicle.Mileage.Report_1

The column with date and time on table Vehicle.Mileage.Report_1= Title

The column containing the VIN = Vehicle Vin_DataCard1

The selection is made on = Datacardvalue4

The result will need to go to = Datacardvalue1

This how i plugged the information you provided.

First(
SortByColumns(
Filter([@'Vehicle Mileage Report_1'],'Vehicle VIN'=DataCardValue4.Selected.Value,
"Date", Descending
)
).'Beginning Mileage'

Also, how can I enter a "0" value if none is found?

Thank you,

[RandyHayes]

@masterp 

So if you are using an EditForm, then it is already tied to all the values.  All you need to do is provide the Item for it to display/edit.  This is done through the Item property of the form.

Again, if you have a gallery where you are selecting these vehicles, then you can set the Item property to yourGallery.Selected

If that is not the case, then tell me more about your scenario. 

I am not sure what you mean by "how can I enter a "0" value if none is found?".  Can you elaborate? 

[masterp]

@RandyHayes 

Yes. I don't have a gallery for the selections.  My Vehicle VIN selections are coming from a SharePoint List as a drop down entry. Please view the image below for additional information.  I know I am not using the right terminology but hoping that the picture displays what we're trying to achieve.

I was able to modify your code and add one entry but it shows the same entry for all Vehicle VINs selected and unfortunately this will not work since every Vehicle VIN has a different beginning mileage entry.

This is the code that filters and shows entry.

First(
SortByColumns(
Filter([@'Vehicle Mileage Report_1'],DataCardValue4="1GNXXXXXXXXX4883"),
"Title",Ascending
)
).'Beginning Mileage'

[RandyHayes]

@masterp 

So it appears that you have an EditForm and you have modified the first datacard with a ComboBox to show vehicle VINs from the same list or a different list?

[masterp]

@RandyHayes 

All data is coming from the same Sharepoint List this includes the dropdown items. 

[RandyHayes]

@masterp 

Okay...almost there.  What is the Items property of your ComboBox?

[masterp]

@RandyHayes 

The code for the items property is:

Choices([@'Vehicle Mileage Report_123'].VehicleVIN)

[RandyHayes]

@masterp 

Okay, then your Default property on the DataCard for Beginning Mileage should be:

Coalesce(
    First(
        SortByColumns(
            Filter('Vehicle Mileage Report_123','Vehicle VIN'=DataCardValue4.Selected.Value,
            "Date", Descending
        )
    ).'Beginning Mileage',
    0
)

 

Your Default property for DataCardValue1 (I believe that is the control you have in your Beginning Mileage datacard) should be : Parent.Default