List with REDUCE and DATETIME_DIFFERENCE

[Paul_Mc]

I have created a page variable named LevelsStandard where Variable value type = List and List item type = Object.

Each object has several properties, including ‘End’, ‘Start’, and ‘Level’…all with Variable value type = number.

The ‘End’ and ‘Start’ values will ultimately reference a timer so are in milliseconds, so my first three objects are;

Start = 0, End = 8471, Level = 1

Start = 8472, End = 16942, Level 2

Start = 16943, End = 25413, Level 3

I am trying to build a formula that references ‘End’ and ‘Start’ to pullback the level. The crucial point is that the formula will reference a timer that I have constructed using the below formula:

DATETIME_DIFFERENCE(appVars.TimeNow,pageVars.TimerStart)

What I have so far is below;

REDUCE(pageVars.LevelsStandard, IF(item.Start<TIMER FORMULA < item.End, item.Level,“unknown”))

At the moment, I am testing using a static number for the TIMER FORMULA and I can get the correct level to pull back. So if I add in 17000, the formula pulls back 3.

But when I replace the TIMER FORMULA with DATETIME_DIFFERENCE(appVars.TimeNow,pageVars.TimerStart), it doesn’t work…which makes me think the REDUCE formula wont work with the dynamic nature I am seeking.

Thanks
Paul

[Mari]

Hi Paul, the object you are looking for is basically the first object whose End property is higher than the elapsed time, right? To find this object and its level, I would use:

FIND(pageVars.LevelsStandard, item.End > DATETIME_DIFFERENCE(appVars.TimeNow,pageVars.TimerStart)).level

[Paul_Mc]

Brilliant, works perfectly.
I have a follow up if that is OK? I have created the same data in Airtable and created the GET COLLECTION as per below;

The data coming through looks like this;

I have then created a formula similar to yours;
FIND(data.ShuttleTimes1, item.fields.End > DATETIME_DIFFERENCE(appVars.TimeNow,pageVars.TimerStart)).fields.Level

It looks to be working and I am getting data from Airtable but it is not working properly because the data coming through is not sorted. It is reasonably obvious that I need to incorporate a SORT function (on “Id” will work (but not “id”), as will “End”). I was just going to add the SORT function in the above FIND but I don’t know how the correct notation to get fields.Id into the formula…it wont accept it. I am missing something really simple…as per usual! Also, is that where I should be sorting it or can I sort it in ‘set data variable’ when I bring it in?
Thanks
Paul

[Mari]

Hi, you can either sort the data when bringing it in or in the formula, just a matter of preference. Did you already try using SORT_BY_KEY() with “fields.Id”?

[Paul_Mc]

Hi Mari

Yes, I did. But I assume I have the notation slightly wrong because it is not working as expected. I used the below but figured the message: Valuation “fields.Id” is not any of the following: “id”, “fields”, “createdTime” meant it is incorrect. It does pull back data…

SORT_BY_KEY(data.ShuttleTimes1,“fields.Id”)

What is one thing I cannot get past is that the Key in the above is in the “fields.Id” format but in the FIND formula the format used is item.fields.End . I dont understand the inconsistency. What I end up with is below, and it starts at level 4 when I run it…

FIND(SORT_BY_KEY(data.ShuttleTimes1,“fields.Id”), item.fields.End > DATETIME_DIFFERENCE(appVars.TimeNow,pageVars.TimerStart)).fields.Level

It is a learning process, I will keep trying. I dont think the SORT (I have also tried ORDER) is working because it is bringing back Level 3 (and if I tweak the formula Shuttle 6) when I launch the timer which is the first record (see above screenshot) in the unsorted population.

Thanks

[Paul_Mc]

Hi Mari

A quick update. This topic answered some of my questions and the ORDER formula seems to be working;

Sort object using SORT_BY_KEY

I have an object: [{“Startnummer”:1,“fields”:{“Song”:"#fuori c’è il sole",“Interpret”:“Lorenzo Fragola”,“Platztipp”:21},“id”:“0”},{“Startnummer”:2,“fields”:{“Song”:“Englishman in New York”,“Interpret”:“Sting”,“Platztipp”:21},“id”:“1”},{“Startnummer”:3,“fields”:{“Song”:“Travelin Band”,“Interpret”:“Clarence Clearw. Rev.”,“Platztipp”:21},“id”:“2”}, {“Startnummer”:4,“fields”:{“Song”:“Route 66”,“Interpret”:“Rolling Stones”,“Platztipp”:21},“id”:“3”}] I am trying to sort this object by “Platztipp”. …

So I have this, and it works!!!

FIND(ORDER(data.ShuttleTimes1, item.fields.Id, “asc”), item.fields.End > DATETIME_DIFFERENCE(appVars.TimeNow,pageVars.TimerStart)).fields.Level

BUT I have another problem. Below is my data in Airtable and as you can see each level is subdivided into several shuttles. I was expecting that if I amended the above (working) formula to end with .fields.Shuttle, the formula would also work to bring Shuttle but it doesn’t. Any ideas?

Thanks
Paul

[Paul_Mc]

Yet another update…my last problem is due to the API only pulling back 100 records from Airtable…looking at the solution to that.

[Mari]

Hi Paul, great to hear that you were able to figure out the formulas and what caused the issue

Regarding the different notation of the formulas: in FIND (or SELECT) you are iterating over an array of objects to see which one has a property or properties that match your criteria – for example in a fruits collection with names and quantities you could be looking for item.name === "banana" && item.quantity === 10 to find the object with 10 bananas. In SORT_BY_KEY there is no need for item since you are not iterating over the objects, just the values of one of their properties (key), and sorting them based on that. Hope that clarifies!