Google Sheets =Filter( Replacement in Appsheet?

AppSheet AppSheet Q&A
1

Can someone help me?
I need to take a clock in entry in table “Time Entries”, and compare that to 2 Columns in another table and return a third column.

Basically, in the table “Time Entries” there’s a time punch.
I need to compare that to a column called “Pay Period Start Date / Time” and another column called “Pay Period End Date / Time” and see where the original time punch falls between those 2 dates, and then return the column called “Projected Pay Date”

I can easily do this with a Filter( in the google sheet, but how can I do this in appsheet?

The google sheet formula looks like this:

=filter(Reference!G:G,J51>=Reference!E:E,J51<=Reference!F:F)

I don’t know if I need to be using LOOKUP or FILTER or what in appsheet. Any help would be most appreciated.

Side note, this would be much easier if someone has a formula to take a “Clock in Date / Time” entry and auto calc the next coming Friday

Thank in advance

2

@Joe_Seiler
Do you have a common reference between these 2 tables?

3

No, other than the row #

4

@Joe_Seiler
So how do you intent to find the matching value in another table? You should have a reference for that.

5

I want to see if appsheet can take the value in the first table named [Clock In Date / Time], and basically query the second table to see which value in [Pay Period Start Date / Time] it’s greater than, and which value in [Pay Period End Date / Time] it’s less than, and then return the value in the same row as from the column [Projected Pay Date].
Just like a =filter( formula would work in a google sheet

6

Every day of the year isn’t listed in the reference table. Just a starting and ending day of the period. However, I suppose I could list every day of the year in the reference table, along with it’s associated pay date. I could make that column a key for that table

7

@Joe_Seiler
You can try with this expression:

LOOKUP(
	FILTER(
		"ReferenceTableName",
		AND(
			([Pay Period Start Date / Time] >= [_THISROW].[Clock-in]),
			([Pay Period End Date / Time] <= [_THISROW].[Clock-in])
		)
	),
	"ReferenceTableName",
	"KeyColumnName",
	"Projected Pay Date"
)
1 Like