@Teodor, can you show an example of a calculated field, using one or more fields from the query? It seems like it’s only looking at the first record in the set and I can’t get it to work.
When I select the Id field, for example, I get query[0].Id:
And, I only get the Id from the first record, repeated for every record in the query.
Not sure I understand what are you trying to achieve. Can you please explain your idea a little more detailed?
Basically, instead of a static value for each record, I want to return a value from one or more fields in the query for each record.
So, in your example, show how to get the “state” and “postalCode” columns from each record and put them into “new_column” instead of “my value”.
That’s currently not possible.
Also - what’s the idea of using the values from the same query like that? I don’t get your idea.
In my case, I want to get the difference between the InDateTime and the OutDateTime fields. Another use case could be calculating the extended price from Quantity and Price.
You can do this on the page where you are using the data directly.
{{column1 - column2}}
OK I think that will work, but I was also wanting to do some very custom data validation and was using this as a first step and to get an understanding of how it works.
I need to show if each record is valid or not, based on if the in and out times overlap with any other punches in a given period and for a given employee. I figured I’d have to read some values from the current record and maybe pass them to a function to make that determination. I’m not sure how to go about it, so if you have any ideas, I’d be happy to hear them.
If you only using 1 database from 1 server then there is no need to use data transformations at all.
Your database query has InDateTime and OutDateTime so if you want to get the difference between the 2 then just run a setValue step after the query named dateDifference or something and a value as Teodor says {{query.InDateTime - query.OutDateTime}} and set it to output.
If you need all the records then add the set value step inside a repeat with the expression set to the query itself.
In your case with wanting to do some checks server side, add a condition step.
So end of day should de something like
query
repeat - expression {{query}}
setvalue - name: dateDiff {{query.InDateTime - query.OutDateTime}}
condition - {{dateDiff < X}}
then
else
EDIT: A tip when doing date calculations, I find it easier to convert to a timestamp to do the calculations
Thanks @psweb for your input here. I’m trying to follow this and see how far I can get.
I can’t get the setvalue expression to resolve. It keeps throwing an exception saying Invalid date. The expression (Value field) is: {{OutDateTime.toTimestamp() - InDateTime.toTimestamp()}} and the Data Type is Number.
What happens if the OutDateTime or the InDateTime is null? I tried putting in a condition in, but couldn’t figure it out.
And, I’m not sure I follow the condition step. What is the condition and what is the step? It won’t let me have an empty step.
Your setValue expression looks right if it comes from a repeat, did you use the picker to select it.
If you date is coming from a database single query step and that step is named myQuery then the expression should be
{{myQuery.OutDateTime.toTimestamp() - myQuery.InDateTime.toTimestamp()}}
If its coming from a database multi query named myQuery then the expression should be
{{myQuery[0].OutDateTime.toTimestamp() - myQuery[0].InDateTime.toTimestamp()}}
This would only get the first record though.
If it is coming from a database multi query which has been used as the expression on a repeat step then it would look like
{{OutDateTime.toTimestamp() - InDateTime.toTimestamp()}}
To be very honest this is always a process of elimination and trial and error.
So set your database multi query, then add your repeat with the expression as the query itself, not the bindings inside the query, then add a few test setValue steps called test1, test2 etc.
Start small and work your way up.
Make the value {{OutDateTime}} and test, if it works, then add the toTimestamp() part to it, if that works, then do more and more and see when it breaks.
I used the picker, but was getting varying results so did some direct editing. I just discovered I get nothing unless I select Output on the repeat AND select all the output fields.
I’m then able to get the value of a single field, but as soon as I create an expression from the in and out fields, it breaks.
This works:
{{InDateTime.toTimestamp()}}
{{OutDateTime.toTimestamp()}}
This does not work:
{{OutDateTime.toTimestamp() - InDateTime.toTimestamp()}}
I just did a small test on my side for you, and all seems to work as expected.
Here are all my steps
This is what the Database multi query looks like
This is the repeat properties {{query}}
This is the Set Value properties
{{mil_date_added.toTimestamp() - mil_photographed_date.toTimestamp()}}
Here is the output of that script
Keep in mind, with Wappler being the same as writing your own code to a degree, you could do this in so many different ways depending on what result you are looking for.
So maybe toTimestamp() is not ideal for you, as it measures in seconds or miliseconds and you have to convert back.
You could user the data formatter {{OutDateTime.dateDiff('years', InDateTime)}} and you can change years to days, weeks, months, minutes, etc.
Your output would then look something like this instead
Entry 1 you will notice is 51 years, haha, unix time start of January 1st 1970 is obviously what the measure of null is, so suppose it makes sense.
I seem to have a problem with the OutDateTime value. I can’t get the value to work for any of the formatters. Only the OutDateTime without any formatting works.
{{OutDateTime.toTimestamp()}}
Produces: “message”: “Exception has been thrown by the target of an invocation.”
“Message”: “Invalid date”
I can’t even get: {{(OutDateTime == null ? 0 : 1) to work.
I get: “message”: “Specified cast is not valid.”
Are you certain in MySQL or whatever database you are using that both columns have been set to datetime?
Secondly, don’t try manually adjust the setValue step, even if you know you want a number output back to you, just leave it as text, there is a difference between a number, and integer, and a number as a string programatically, so just leave it as text inside the setValue step.
Please show a screenshot of you database query
The database is MSSQL Server and yes, both columns are of type datetimeoffset(7)
I’ve scanned down over the data and can’t spot anything odd, but I could be missing something. Not sure what to check for to validate the values.
I have the setvalue data type set to Text and:
This works:
{{InDateTime.toTimestamp()}}
But this does not:
{{OutDateTime.toTimestamp()}}
Here is my database query:
Looks and sounds like everything is good to me, just to test lets get rid of any NULL values being returned in the data, to see if its that possibly.
Just add a condition inside the database query to say InDateTime is not null OR OutDateTime is not null
That should get rid of all entries where there is a NULL value.
Lets see how that test goes.
Great idea. Just ran that on just the OutDateTime and that works fine…
So, it’s the null values that are causing the problem. But your query had a null value??
Yeah but I am on a Node.js v14 server with MariaDB 10.5.x, I assume it just handles that type of stuff differently to the MSSQL server.
Are you using ASP.NET or the classic one out of interest.
Yes, ASP.NET
