Solved: Expression to get list of rows within date/time ra...

Jake_Naude · 06-24-2020 10:26 AM

Hi,

Please could someone advise of the expression to get a list of just the green rows?

Something like:

[select_asset_type] are all the same as each other i.e. “CAT6A”
[room_sid] are all the same as each other i.e. “2-624”
[survey_status] are are same as each other?
[last_changed_on] rows are within 2 minutes of each other?

UseCase:

In the green rows I just surveyed 5 [asset_name_text] EnumList in one survey form and split them into 5 new rows in same table OnSave.
In the last row I surveyed a different thing (LPR006).
I now need to start new survey form with the green rows in the [asset_name_text] EnumList?

Thanks in advance…

Jake_Naude

@Rich,

I’ve included two new helper columns:

[number] - the number of the survey or grouped/related survey rows.
[previous] - the EnumList items from the form before it was saved and split into many.

That might make it easier for the expression to identify the group…?

In adding the helper columns I realise I’ve just answered my own question cause they make it easy to identify the most recent group of surveys… It amazing how the act of presenting a question on this forum brings clarity and perspective to the issue…

Thanks @Rich

View solution in original post

Rich_E

Where is the user coming from when they start this new survey for the green rows?

Jake_Naude

Hi @Rich,

I’m not sure I follow…?

I may have caused confusion above. The action is OnSave. The table is survey.

All surveys will always start from the survey table’s survey form.

In real terms they’ll be in the same room (2-694) or may have moved into a new room?

Rich_E

When these rows are identified where is the formula being run? In this example above how do you know “CAT6A” and “2-624” are the values that need to be in the expression?

Jake_Naude

Good question… the thing is the CAT6A is a cable type and could also be one of two other cable types in the next or another survey (CAT6A, Fibre, Power).

My thinking was to add a new button to the form that asks if I’m surveying a single [asset_name_text] (Asset Name) or if I’m surveying multiple cables in [asset_name_text] i.e. (CAT6A, Fibre, Power)

They may be a bit of a red herring, but I thought it’d be a useful way to help isolate the [last_changed_on] range… i.e the most recent batch of rows for the same [asset_name_type] where [last_changed_on] is withing 2 minutes of each other.

I could then use that list as my [asset_name_text] EnumList when I select the new 'surveying multiple cables button?

Hope that makes sense?

Jake_Naude

Clarification point…

The next survey would be the same [asset_name_text] as per the green rows… i’e I’m going to survey those same things again…

Jake_Naude

The gist of what I’m trying to do is add two new buttons to do the following:

Two New Buttons: (Single Item) and (Multiple Assets)

(Single Item) would say “I remember what you surveyed the last time you surveyed a single item”

(Multiple Assets) button would say “I remember what you surveyed the last time you surveyed multiple items”

Rich_E

Do you have an asset table? My recommendation would be to have the survey table ref back to a record, that way you could easily query the questions. It would be fairly expensive to have AppSheet compare each record to all other records. Not saying it can’t be done but it could slow down your app.

If you did want to have an expression that compared each record to every other record this would probably work for you.

select(survey[id], count(select(survey[last_changed_on], and([select_asset_Type]=[_thisrow].[select_asset_Type], [room_sid]=[_thisrow].[room_sid], [last_changed_on]>=[_thisrow].[last_changed_on]-“000:02:00”, [last_changed_on]<=[_thisrow].[last_changed_on]+“000:02:00”)))>0)

This formula will calculate a select statement for each record in the table, and count if there are any records that match the criterion you wanted above. For any given record if it counts more than zero matches then that record’s id will be returned.

Jake_Naude

Hi @Rich… Thanks a million for above suggestion… I’m going to get stuck in and see how I get on with that and I’ll report back with progress.

On the processing burden issue… You have a good point… I do have an asset table with a Ref column in this survey table called [asset_id] and there is also an [asset_name] and [asset_type] column in the asset table.

I wouldn’t know where to start to do this and involve the asset table…? Is it tricky…? I can post screen shots of the tables if you don’t think it’ll be too difficult?

Thanks again…

Jake_Naude

Hi @Rich,

I have your expression working in a VC…

Thanks for your time and for pointing me in the right direction.

SELECT(survey[id],
COUNT(
SELECT(survey[last_changed_on],
AND(
[select_asset_type]=[_thisrow].[select_asset_type],
[room_sid]=[_thisrow].[room_sid],
[last_changed_on]>=[_thisrow].[last_changed_on]-“000:02:00”,
[last_changed_on]<=[_thisrow].[last_changed_on]+“000:02:00”
)))>0)

I’m having a little trouble changing the logic to return a slightly different result and wonder if you could shed some light please…?

The current expression returns all survey results if any survey was completed within 2 minutes.

Ideally, I’d like to see - if possible:

just the surveys for the green rows where they share
same [select_asset_type],
and same [room_sid]
and [last_changed_on] for those rows is within 2 min of each other (but any time ago)

By starting to look from the bottom up, I’m trying to catch the ‘most recent’ group of surveys to show them in an EnumList.

Thanks in advance… Cheers…

Jake_Naude