Hi guys,
Can any one help me with what i want to do : In fact i have a WO table with about 100 WO per day and in this table i have two based dates columns. What i want to do is to be able to, at the start of the app, substract the two based dates columns and save the results in an other column of the same table. Is it possible to be done or not knowing that i can’t determine the number of records of the table. So, it has to be something automated that will do this on all records.
thanks,
Can you post an example of the work order table structure? What you’re asking for is possible, but I can’t answer specifically what to do without clarifying more about exactly what you want to calculate and where it should display.
Hi @RussWaddell,
Yes of course. Here is my table structure.
In fact, what i want to do is, to susbstract the creation date - date et heure actuelle of all records and then store the result in the column Lambda OF to be able to sort the table acoording to this column knowing that i don’t have the number exact of the table records because it’s variable i mean we can go untill 100 OFs per day.
thanks,
Thanks, I’m working on creating an example based on what you shared. Here’s the documentation I’m using: How to Run Date and Time Calculations in Apps | Tulip Knowledge Base - Support for Building Operations Apps
Hi @RussWaddell,
I have use this article to calculate the due date of WO but i didn’t see how to do what i have asked in this topic.
One more thing, i have seen that when we do these calculations, then sunday and saturday are included knowing that here we don’t work these days. So, i have to take them in consideration when calculating the due date no?
thanks for your help,
Hi @RussWaddell,
Can we use Table API or Customer widgets or even node red to do so? (be able to do susbstraction on all records of a table at the same time).
thanks,
Alright I got some help on these answers for you @FahdERM. There are two approaches to use, both of which use a variable in the third column: First, populate the calculated column of the table with a variable at the time of table record creation. Secondly, you can loop-to-update and fill that variable to each cell of the calculated column.
@freedman recorded a video walkthrough of both options during office hours today General Office Hours (2022-07-07 11:06 GMT-4) - Google Drive.
It’s possible to use Table API or custom widgets to accomplish the same, although I don’t have an example of exactly how to do that.
Hi @RussWaddell,
Thank you and @freedman for this video. I have foud this very very interesting and helpful. Just last thing, i have seen that when i susbstract days from a date : image that we today is 11/7 and my deadline is one day so when i will do 11/7 minus 1d normally it susbstract sunday and saturday and shows me the date 8/7 because they are two days off. Is there a solution for this or there is no one for the moment.
Thanks very much for the video,
@FahdERM I believe that you are trying to get the difference between two dates, a start date and an end date, but you would like to consider and remove weekend days. This is probably possible using a number of methods, below is one of them.
First, we need to check if the days are in the same week. If so, then the calculation is end_date - start_date. If they are different weeks, we need to subtract two days for every week.
Here is an example of this working, with the logic split out so that it is easier to read:
And here is the expression in a single line that you can use:
if(rounddatetime(@Variable.start date , 'week') = rounddatetime(@Variable.end date , 'week'), @Variable.end date - @Variable.start date , (@Variable.end date - @Variable.start date ) - seconds_to_interval((interval_to_seconds(rounddatetime(@Variable.end date , 'week') - rounddatetime(@Variable.start date , 'week')) / (7 * 60 * 60 * 24)) * 2 * 60 * 60 * 24))
Hi @freedman,
Thanks for these elements this will be very helpful for me for an other use. But, i mean in my question below is that i want to be able when i add for example 3 days to the date of today : 08/07/2022 to be able to extract the weekend days from my operation and instead of displaying 11/07/2022 (09/07 and 10/07 are counted and what i want to eleminate) the result displayed is 13/07/2022.
Thanks for your help @freedman,
@FahdERM This one is a bit tricky but also doable. We can consider weekends in the same way as before except we need to consider one other thing. Someone may want to add 1 day to a Friday date, which would put the new date on a Saturday… which occurs in the same week. So we need to account for any dates that land on Saturday.
You can do it like this (broken out into steps)
or just put it all into one big expression like this (it’s a monster):
(@Variable.date + seconds_to_interval(@Variable.number of days * 60 * 60 * 24)) + (seconds_to_interval(interval_to_seconds(if(rounddatetime((@Variable.date + seconds_to_interval(@Variable.number of days * 60 * 60 * 24)) , 'week') = rounddatetime(@Variable.date , 'week'), seconds_to_interval(0), rounddatetime((@Variable.date + seconds_to_interval(@Variable.number of days * 60 * 60 * 24)) , 'week') - rounddatetime(@Variable.date , 'week')) / 302400) * 60 * 60 * 24)) + (if((((@Variable.date + seconds_to_interval(@Variable.number of days * 60 * 60 * 24)) + (seconds_to_interval(interval_to_seconds(if(rounddatetime((@Variable.date + seconds_to_interval(@Variable.number of days * 60 * 60 * 24)) , 'week') = rounddatetime(@Variable.date , 'week'), seconds_to_interval(0), rounddatetime((@Variable.date + seconds_to_interval(@Variable.number of days * 60 * 60 * 24)) , 'week') - rounddatetime(@Variable.date , 'week')) / 302400) * 60 * 60 * 24))) - rounddatetime(((@Variable.date + seconds_to_interval(@Variable.number of days * 60 * 60 * 24)) + (seconds_to_interval(interval_to_seconds(if(rounddatetime((@Variable.date + seconds_to_interval(@Variable.number of days * 60 * 60 * 24)) , 'week') = rounddatetime(@Variable.date , 'week'), seconds_to_interval(0), rounddatetime((@Variable.date + seconds_to_interval(@Variable.number of days * 60 * 60 * 24)) , 'week') - rounddatetime(@Variable.date , 'week')) / 302400) * 60 * 60 * 24))) , 'week')) = seconds_to_interval(6 * 60 * 60 * 24), seconds_to_interval(2 * 60 * 60 * 24), seconds_to_interval(0)))
1 Like
Hi @freedman,
Thank you very much for these elements. I have tested this today and it works very well. Thanks for the idea.
One More thing, after validating group of steps, i have to save the user and the date in the same column of a table :
I have wrote it like this but in the table, i have the user name and a strange format (i think it’s an interval) of the date :
Do you have an idea on how to display the user name and the date ?
thanks,
@FahdERM, glad to hear that the suggestions worked for you. If you’d like to display date in a text format that you are familiar with, I would use the following function:
You can format the date however you’d like. For more options in formatting the date (beyond YYYY-MM-DD) refer to this page: Moment.js cheatsheet
Thanks
Mark
Hi @freedman,
Hope you are doing well.
In fact, i have tested you solution of the extraction of the weekends from my calculated date and it worked when i have done this. But yesterday, i tried to excute the app again and i have discovered that calculations are no more correct and it didn’t took the weekends extract on consideration and i don’t know why. I saw that changing the time zone affect these calculations but even when i have choose the right timezone my date calculation is false.
Can you help with this please?
NB : Just to have an idea : for my case we don’t add days to the principal date but we substract days from that date so i think the fact that the two dates (old one and new one are not in the same week is the problem right?
thanks,
@FahdERM since this thread started we have added some new expressions into the editor which should help with troubleshooting and readability: subtract_time() and add_time()
It may make sense to rebuild the logic with these functions and then share what you’ve got.
