How to write an app formula to find the last row with certain columns match

[HomeShareMa]

I have table 1 given data as below in Appsheet. Table 1 is for data input regardless duplication. the last row is the latest record. VendorNo may change any EventDate or BoothNo as they wish. However the last rows are their final decision.

Table 2: BoothNo & EventDate are given. I tried app formula like below:

ANY(SELECT(DataTable[VendorNo]
,and(
[_THISROW].[BoothNo] = [BoothNo]
,[_THISROW].[EventDate] = [EventDate]
)))

Table 2 results is printing everything. All I want is trying to print the final selection.
Like VendorNo chose BoothNo A1 initially, and then the final selection is A4. Therefore A4 should be printed but not A1.

The right result should be like following:

Any thoughts? 

[Craig_QREW]

I recommend adding a date time as to when this was requested.

If you do that then you can use the FILTER() function for these responses and you can order them by the date time in decreasing order that way the most recent one is at the very beginning. This can be selected using INDEX(...,1) and then can be found using ANY() Select() like you are already doing. to get the final choice they gave you. If you'd like to do that you may, and I have links below to these functions. 

Instead of having them submit a new form each time they want to change a selection though. It would be best to simply let them edit that form and change the response that way instead. Because this complicated function will impact your apps performance. 

I hope this helps one way or another. Please let me know if you still need help and I will happily assist. 

Craig 

QREW Technologies

FILTER() 

ORDERBY() 

INDEX() 

SELECT() 

ANY() 

[HomeShareMa]

Hello Craig,

I did try 2 codes below and find the results are the same. They also show up all the result but not the latest record.

INDEX(LIST(
ANY(SELECT(DataTable[VendorNo]
,and(
[_THISROW].[BoothNo] = [BoothNo]
,[_THISROW].[EventStartDate] = [EventStartDate]
)))),1)

And below seems has the max _RowNumber function but still don't work(still show all result):

ANY(SELECT(DataTable[VendorNo],([_RowNumber]=MAX(SELECT(DataTable[_ROWNUMBER]
,,and(
[_THISROW].[BoothNo] = [BoothNo]
,[_THISROW].[EventStartDate] = [EventStartDate]
))))))

Actually I did have input Date and Input Time in Table1 for participants' log. However, It seems not possible to have Datetime in Table2. Because Table2 only has which data and which booth left. 

Filter function seems not working in this scenario. Filter will result in more than 1 value if matched.

The only one thing would like to do is while Table1 input is done. Table2 is trying to get the latest row with unique EventDate and VendorNo.

Now all I have tried 2 codes are showing up all EventDate and VendorNo who have selected the BoothNo.

If vlookup is trying to get the first value. I think I am trying to get the last value if matched.

Any thoughts? Thank you for your time.

[WillowMobileSys]

You want to use the MAXROW() function.  Use [_ROWNUMBER] as the max value column and insert your filter criteria on EventDate and Booth.  See the article below for details

https://help.appsheet.com/en/articles/2357310-maxrow

NOTE:  MAXROW() returns the row ID only.  But you can use this in your expression like this:

ANY(SELECT(DataTable[VendorNo], 
         [Key Column] = MAXROW("DataTble", "_ROWNUMBER, 
                               AND([_THISROW].[BoothNo] = [BoothNo], 
                                   [_THISROW].[EventDate] = [EventDate]) 
                        )
     )
) 

[HomeShareMa]

Hello WillowMobileSys, I got error message when using the given formula. May I know what should I fill in with [Key Column]?

[Lynn]

@HomeShareMa 

You need to replace "Key column" with the name of your key column.

[WillowMobileSys]

As @Lynn  mentioned, "Key Column" is to be replaced with the name of your column that is set as the key in the DataTable.

Also, I am noticing that there is a missing " after _ROWNUMBER which is why you are getting the error. Add that double-quote.

[HomeShareMa]

What I revised the formula as below:

ANY(SELECT(DataTable[VendorNo], 
         [_ROWNUMBER] = MAXROW("DataTable", "_ROWNUMBER", 
                               AND([_THISROW].[BoothNo] = [BoothNo], 
                                   [_THISROW].[EventStartDate] = [EventStartDate]) 
                        )
     )
) 

The formula has no error message this time. But all data show up again like previous codes I tried before.

I have no key column in DataTable. As I understand that key column must be unique. However DataTable is a data warehouse. It doesn't allow update but allow to add new data. Please see the DataTable (Google Sheets)below:

The concept of DataTable is for people who would like to join event and allow them to add new row for which date and which booth. Sometimes people may regard what they chose, so they can only add new row to claim their new application.

Therefore, the formula concept is getting the last row of DataTable with VendorNo, EventStartDate & BoothNo matched. This can ensure that VendorNo(Who) is setting up BoothNo(Where) in EventStartDate(When).

The concept is not difficult. Is it possible to do it without primary key in DataTable (key column)?

[WillowMobileSys]

---

@HomeShareMa wrote:

...But all data show up again like previous codes I tried before.

---

There must be some mis-communication,  The expression can ONLY possibly return a single VendorNo value or nothing.  So when you say "all data show up", you will need to explain more about what you are seeing in your app because otherwise we've no clue what you are referring to.

Also note, every table MUST have a key column.  That is true in ANY relational database.  It doesn't matter if it is a warehouse data table and that any of the columns could have repeated data.  There must be a key column.  AppSheet will automatically assign the [_ROWNUMBER] column as the key IF no other column has been set as the key.  My preference is to ALWAYS add a dedicated column as the key that is assigned a random unique value using AppSheet's UNIQUEID() function - aka a dataless key.  [_ROWNUMBER] is NOT a reliable key column.

The BIG question to you is... In your table named "DataTable", is the column of [_ROWNUMBER] set as the key?

If it is the key, then your revised expression looks to be correct - based on what you have provided us so far.

And your right, it's not a difficult concept.  But it is difficult to provide help with an issue when we are not provided the proper details. 

We need to understand where you are using the expression in the app and why you think the expression is not working.  It would also be helpful to see the configuration of your table and its columns AS WELL AS an understanding of the data. 

We understand your data but have no clue on the specific issue you are having in the app nor understand how the table/columns are configured to know if that might be impacting things. 

To help further, you'll need to describe your problem in the app and since you think it's the above expression contributing to the problem, it would be beneficial if you could show us the DataTable column configurations - a screenshot of the column list is a good start!

[HomeShareMa]

Thank you WillioMobileSys. Answer your first question. DataTable[_RowNumber] is the primary key. 

There are only 2 table involved in my case. The first one is DataTable, which is used for people to log their wish for joining booth number (BoothNo) in which date (EventStartDate) and by whom (VendorNo). The latest record should overwrite previous record.

For example: v001 (VendorNo) claimed to join 2022-11-11 (EventStartDate) with C01 (BoothNo). And then v001 regrets for his choice and input again like: v001, 2022-11-11, A01. then the system should mark the latest record, namely A01 but not C01 anymore.

Another table is BoothConfirmed. EventStartDate & BoothNo will be given and the formula should have to according to EventStartDate & BoothNo to find out which Vendor selected in DataTable.

Below is BoothConfirmed-Appsheets: (Yellow highlight are important columns and red frame is the formula I try to write properly)

Red frame formula is :

ANY(SELECT(DataTable[VendorNo], 
         [_ROWNUMBER] = MAXROW("DataTable", "_ROWNUMBER", 
                               AND([_THISROW].[BoothNo] = [BoothNo], 
                                   [_THISROW].[EventStartDate] = [EventStartDate]) 
                        )
     )
) 

Below is DataTable-Appsheets: (Yellow highlight are important columns)

Below is BoothConfirmed-GoogleSheets: (Yellow highlight are important columns)

Below is DataTable-GoogleSheets: (Yellow highlight are important columns)

Below is DataTable-Output: (for your reference)

Below is BoothConfirmed-Output: (Same vendorNo should not appear on the same EventStartDate. And should only display the latest record. Like the output below: v001 should assign to D02 only in 2022-11-11. D01 record should left blank)

How can I rewrite the red frame formula to get the latest record with VendorNo + EventStartDate + BoothNo? 

[WillowMobileSys]

Wow!  This is exactly what is needed.  Great job on gathering the details.

I cannot see in your last image where EventStartDate is.  And I don't see from the BoothConfirmed table how that value is getting set.  Your expression depends on that date being there.  If it is not set, the expression for VendorNo will be blank.

EDITED:  Oh, I also just noticed that in your BoothConfirmed table that EventStartDate is a REF while in the DataTable it is a Date.   Ref's cannot be compared directly to a data value - even if the Ref value and the data value are exactly the same.  They represented differently in the system and are not considered equal.

Either EventStartDate in the BoothConfirmed needs changed to a Date type OR you need to dereference it to the actual date column - i.e. [_THISROW].[EventStartDate].[Start Date] where [Start Date] is replace by the actual name of your date column.

[HomeShareMa]

Oh.. If EventStartDate couldn't be primary key. Things are getting complicated. However you are alright. EventStartDate  shouldn't be a primary key. If some day future there are 2 events in one day. That would be a problem.

When I am using EventStartDate as Ref. Ref_Rows in EventInfo(It is another table). I am getting the right format to show red frame part as BoothConfirmed. See below:

The layout and format are perfect but the data is incorrect. (All I am trying to do is fix the formula here)

Below I am trying to change back BoothConfirmed[EventStartDate ] as Date as picture below. And add EventName in Google Sheets. Regenerate the table and edit BoothConfirmed[EventName] as Ref.

And I also have to change Ref_Rows in EventInfo table as below:

And finally I found nothing in my EventInfo-Detail:

[WillowMobileSys]

Hi! 

What you are doing isn't complicated. I think you just have a mis-understanding in how certain column types work.  You'll get there!  Hint - if you learn a little more about how relationships between tables work, you'll come to understand that if you setup the tables correctly, AppSheet will automatically create all of the connections, assign the necessary expressions and create the needed views for you (see link below).  You are really doing all of this manually - the hard way!

Ref columns are NOT primary key columns.  They are simply pointers to rows in another table.  They store the value from the column marked as key in that other table to indicate which row (or rows) they are referencing since keys must be unique.

For your REF_ROWS("BoothConfirmed", "EventName") function to work, there must be a column in the "BoothConfirmed" table that houses the key value for the table this expression is used in  - in your case that is "EventName".  However, the REF_ROWS function then passes the key value from the current table.  AppSheet will automatically match the passed key value to the value in the Ref column in that other table "BoothConfirmed".  

So your REF_ROWS expression should be:

REF_ROWS("BoothConfirmed", "EventStartDate")

If you decide to change the key in the EventInfo table then you'll need to change this expression to match.

References Between Tables

https://help.appsheet.com/en/articles/961426-references-between-tables

[HomeShareMa]

Thank you WillioMobileSys for sharing Reference table and connection. I still have to digest.

However, Shall we go back to the formula that I ask for help?

How to write a formula for BoothConfirmed[VendorNo] that only show the latest record of EventStartDate & BoothNo & VendorNo?

[WillowMobileSys]

Sure!  To fix, you need to do what I mentioned here:

EDITED:  Oh, I also just noticed that in your BoothConfirmed table that EventStartDate is a REF while in the DataTable it is a Date.   Ref's cannot be compared directly to a data value - even if the Ref value and the data value are exactly the same.  They represented differently in the system and are not considered equal.

Either EventStartDate in the BoothConfirmed needs changed to a Date type OR you need to dereference it to the actual date column - i.e. [_THISROW].[EventStartDate].[Start Date] where [Start Date] is replace by the actual name of your date column.

---

Assuming you wish to keep this Ref column then you need to apply the partial expression above like so:

ANY(SELECT(DataTable[VendorNo], 
       [_ROWNUMBER] = MAXROW("DataTable", "_ROWNUMBER", 
                AND([_THISROW].[BoothNo] = [BoothNo], 
                    [_THISROW].[EventStartDate].[EventStartDate] = [EventStartDate]))
   )
) 

I hope this helps!

[HomeShareMa]

Good day! I think below double [EventStartDate] is the expression of dereference.

 [_THISROW].[EventStartDate].[EventStartDate] = [EventStartDate]

 And I try to input the given formula below under the red frame:

ANY(SELECT(DataTable[VendorNo], 
       [_ROWNUMBER] = MAXROW("DataTable", "_ROWNUMBER", 
                AND([_THISROW].[BoothNo] = [BoothNo], 
                    [_THISROW].[EventStartDate].[EventStartDate] = [EventStartDate]))
   )
) 

The output is showing all the input again that I have in DataTable as below(Red frames are duplicates. Namely C02 & D01 should be abandoned) Below view has been grouped by EventStartDate in UX. So below is just only EventStartDate ='2022-11-11', which is very good setting for me.

Look back to the DataTable. VendorNo v001 & v005 selected twice and the formula should fetch the latest one with EventStartDate & BoothNo.

DataTable is a Data Warehouse a-like table. Keep adding new row and never allow to edit(update). So if vendor changes their mind who have to write the new row below to overwrite the previous record.

Yellow highlighted above should be shown in EventStartDate ='2022-11-11' 

Green highlighted above should be shown in EventStartDate ='2022-7-18' 

Pink highlighted above should be shown in EventStartDate ='2022-5-1' 

But now the formula still showing all the data in DataTable including row without highlighted.

May I know how to solve it?

[WillowMobileSys]

The expression is working as you had requested.

The issue you now have is not with the expression selecting the Vendor for the Booth. 

This issue is now in HOW you are allowing the Vendors to be assigned to booths.  Once a booth is assigned to a Vendor, it should not be available to be assigned to another Vendor.  For booths already assigned, you should remove them from the available booth selection list

For example:

In your Yellow highlighted area you have Vendor v004 and v005 assigned to BoothNo=C01.

This should not have been allowed in the entry Form.  Otherwise, how can you know which vendor is supposed to be assigned?  You can't just say the last one assigned because then that means you are allowing assignments to be overridden and will be in an endless loop of assignments.

Another question?

Why can't vendors be assigned to more than 1 Booth?  Is it a rule they can have only 1 booth? 

If a vendor can have 1 and only 1 booth, then your expression to find the assigned vendor by booth needs to re-designed.  It will get more complicated. 

A Better Approach

I don't know your app and how you are allowing booth assignments.  If that is being done within this app (or another appsheet app), then a MUCH easier approach is what I suggested above - once a vendor has been assigned a booth, then remove both that booth and that vendor from the selection lists of the assignment Form.  If the vendor decides to move, or needs moved, then that vendor must be un-assigned first, releasing that booth for another vendor, and then re-assigned to a different booth removing it from the selection list.

I hope this helps.  Let me know what you decide

[HomeShareMa]

Wow.... you have a very exceptional clear logic.

You are right that 1 VendorNo is eligible to apply 1 or more booths within EventStartDate. Therefore, if VendorNo decided to drop the BoothNo. Un-assigned BoothNo is the MUST step.

If another VendorNo assigns the same BoothNo which has been assigned before should be rejected until un-assignment step finished.

Do I need to add 1 more column in BoothConfirmed for recording un-assignment? 

How to rewrite that complicated formula to fulfil the right assignment?

Thank you 🙂

[WillowMobileSys · Accepted solution]

---

@HomeShareMa wrote:

You are right that 1 VendorNo is eligible to apply 1 or more booths within EventStartDate. Therefore, if VendorNo decided to drop the BoothNo. Un-assigned BoothNo is the MUST step.

Do I need to add 1 more column in BoothConfirmed for recording un-assignment? 

How to rewrite that complicated formula to fulfil the right assignment?

---

Again, I don't know how you are getting the entries into your DataTable.  IF you are using an AppSheet Form to assign Vendors to Booths, you should prevent the ability of Vendors picking Booths that are already assigned, i.e. remove those choices from the dropdown list, in that Form.  This will eliminate the need to add to the expression you have built.

If you cannot adjust the entry Form to prevent picking assigned booths then...Yes...you would need a way to indicate an unassignment action.  I would use a Yes/No column [Assigned?] that initializes to TRUE.  Then you can adjust your expression like this:

ANY(SELECT(DataTable[VendorNo], 
       [_ROWNUMBER] = MAXROW("DataTable", "_ROWNUMBER", 
                AND([_THISROW].[BoothNo] = [BoothNo], 
                    [_THISROW].[EventStartDate].[EventStartDate] = [EventStartDate],
                    [Assigned?] = TRUE ))
   )
) 

 Those DataTable rows that indicate unassigned, [Assigned?] = FALSE, will be skipped and not considered in your expression to show the Vendor assigned to the Booth.

[HomeShareMa]

Thank you WillowMobileSys, you are not only solving my problem. You also gave out the clear logic and instruction to guide me how to understand more about Appsheet. 

Once again. Please accept my heartfelt gratitude.