I would like to create assembly serial numbers/table IDs with the format YYYYMMDD-x with the number after dash being the -nth build of the day. My main issue was the variable I was using to track the number of builds in a day was resetting to zero every time I opened the app. Is there a way to save the variable data while the app is closed so it doesn’t reset each time? And is there a way to reset the variable daily automatically?
Hi @lflaherty,
Using a variable to track the build number is not reliable so i will stay clear of that method.
What I suggest is :
Store the serial number in a table. (If you have not done it already)
Run a table query for the date created. The date will be supplied as an app input.
Sort the results “new to old”
Limit the results to 1. This way we get the most recent serial number for that day. If no serial numbers were created that day then there are no results. Then you know you have to start numbering from the beginning.
Create a table aggregation to retrieve the result
In the app create a trigger to store the table aggregation result.
Then use a combination of TEXTTOINTEGER(), MID() or TRIM() in the expression editor to extract just the number and then add one to that.
Technical Details of the Expression Editor (tulip.co)
Sorry for the very high level explanation but the info on this thread give you more info. There is a youtube vid there as well.
Using Table Aggregations to auto-increment ID - Show and tell - Tulip Community
There are other discussions in the community about using Table APIs. But IMHO that is not required for your application.
1 Like
Thank you so much! This is very helpful.
I created the table query but when I tested it the problem I ran into was it only loading records with the exact date and time. Is there any way to adjust the query so the time doesn’t have to be the same?
oh yes, i should have thought of that. sorry
You will need to do something like this. Check within a range of date and time.
Now the querry needs 2 app inputs. Start time and End time.
there is a few ways to set the start time and end time. Chose the combo that best works for you.
I think you can calculate the end time by using ADD_TIME() in the expression editor.
But the challenge i see is getting the current date and time then removing the hours, mins and secs to calculate the start point at 00:00:00 of a given day.
Some of these functions should give you what you need.
- DATE_TRUNC()
- ROUNDDATETIME()
- DATETIMETOTEXT()
- TEXTTODATETIME()
- LEFT()
Technical Details of the Expression Editor (tulip.co)
have a try let me know if you need help with the expression.
One thing i forgot to mention…
Table aggregations, and any form of writing to tables does not work in Test (Dev player) you have to use the run button and use the Tulip player.
I have set up one variable for the start time that is the current day and time of 00:00:00 but how would I set up a end time in a variable where I add essentially 1 day so the end time is the current day but with a time of 23:59:59/end of the day? I couldn’t find ADD_TIME in the expression editor either.
Hey @lflaherty -
ADD_TIME() and SUBTRACT_TIME() will come with your update to LTS 9, it is a relatively new function.
The way I would approach getting to the end of the day is:
Pete
2 Likes
@Pete_Hartnett to the rescue 
@Pete_Hartnett What it is resolution (smallest unit) for “Current Date and Time” in Tulip. Is it seconds or milliseconds?
The reason I ask is if its milliseconds, the end of the day is actually 23.59:59.999
In this case @lflaherty you are better off adding one complete day (ie 86400 seconds) and then setup the query to be “is before” instead of “is before or at”
Behind the scenes, we do store (and evaluate conditionals) on the ms level. Adding 86399 to the beginning of the day would evaluate to 23:59:59.000 so 23:59:59.452 would evaluate as not within that range. Because of this, I would also do @Rakitha’s approach and add 86400 and adjust the conditional accordingly.
Pete
1 Like