Appsheet check if new rows are different from existing ones

[narcis]

Hello,

I am having a couple of issues with setting an automation into my appsheet.  I have a scheduled daily import with the data containing an employee email, competence, number of iterations done and current level for that competence. What I need to achieve is when an employee obtains a higher level of competence for one iteration, he will receive an email notification.

I guess that I need to find a way to check if a newly added row is having a different level for the same employee and competence , but I am not sure if this is possible in Appsheet.

Thanks,

Narcis

[WillowMobileSys]

Yes, it's certainly possible.  How hinges largely on how you are importing the data.  Is it with an AppSheet scheduled automation?  Are you importing data in bulk rows or one at a time?

If you are importing one row at a time with an AppSheet automation, you can tie into that process, search for a matching existing row and perform your comparison.

If you are importing in bulk, then you would want a second automated process that runs after the import.  It would pick all of the newly imported rows, check for any matching existing rows and, if found, perform your desired comparison.

[Marc_Dillon]

Or if the import is with appsheet's "import csv" action, it should run automation for every imported record.

[narcis]

Hi,

Thanks for the replies. I am not sure how the automation for checking the new level for each employee and competence would look like. How would the condition look for something like that? I can't seem to see what type of action should I choose and how to set it up. 

The formula in the referenced rows is  - FILTER('COMPETENCELEVELS', AND([Angajat] = [_THISROW].[Angajat], [CompetentaID]=[_THISROW].[CompetentaID], Today() - [Data Obtinere]=1)).[Nivel]

Narcis

[Marc_Dillon]

That's an Action, not an Automation.

Also, it seems you missed the part where we'd first need to know how you are importing the data.

[narcis]

The data is imported with a scheduled automation in appsheet. The automation is run daily with an action to add new rows in the table with the timestamp for the day. 

[narcis]

@Marc_Dillon do you think is doable? is there any other information you would need?

[Marc_Dillon]

I'm still not sure what you're talking about exactly, I'm not aware of any Appsheet automation that can import data. 

But maybe you just need an expression that will return the value from a previous record? You can do it with something like this:

LOOKUP(
  MAXROW( table , timestamp-or-rownumber , AND( [identifier] = [_THISROW].[identifier] , [timestamp/rownumber] < [_THISROW].[timestamp/rownumber] ) ,
  table ,
  key-column ,
  value-to-compare
)

[narcis]

Thanks, something like is what I need. But for some reason the formula doesn't return any values, here is the syntax:

LOOKUP( MAXROW( "COMPETENCELEVELS", "_RowNumber" , AND( "Angajat" = [_THISROW].[Angajat] , CompetentaID = [_THISROW].[CompetentaID], Date((TODAY() - 1)) = Date([Data Obtinere]) )) , "COMPETENCELEVELS", "_RowNumber" , "Nivel" )

[Angajat] data type is email, [CompetentaID] is text and [Data Obtinere] is datetime. The issue seems to be at the MaxRow calculation, which doesn't return anything. What could be the problem here?

[Marc_Dillon · Accepted solution]

1. "Angajat" isn't an email address.
2. Your column to maximize really should be the same as the one being checked with '<' in the extra conditions.
3. Speaking of the '<', you didn't use it, but instead are equaling to yesterday. Not sure why you did that.
4. Stop using row number as your key column.