I need help on how to subtract dateField2 from DateField1 in a dynamic flow table.
The table row would increase or decrease depending on how many entry the user wants to input.
I have a table with 4 columns in it. in the first column I have a date from and a date to field and a drop-down list . The drop-down list has list items which i entered in 0,0.5,1,1.5 and so forth up to 10 in increments of 0.5
What I want to do is when I change the "date work from" or DateField2 and the "date work to" or DateField1 the dropdown list would change but will also allow the user to override it as well.
How do i create this drop down?
I tried creating an invisible cell that would have DateField2-DateField1 and hoping that I can have dropdown = that and find ways to tweak from there but have not been successful.
Hi, if you will be using FormCalc you can use the following:
var DateFrom = Date2Num(DateField1.rawValue, "YYYY-MM-DD")
var DateTo = Date2Num(DateField2.rawValue, "YYYY-MM-DD")
var answer = DateFrom - DateTo; answer is the number of days between both dates...
Thank-you for your reply.
I have tried that but it does work the answer shows as zero
So what I did is I tried changing it a bit because its in a dynamic table or flow table that will allow user to add or delete rows I added in [*] but still wasn't able to get it to work. This is what I have
var DateFrom = Date2Num(DateField1[*].rawValue, "MM/DD/YYYY")
var DateTo = Date2Num(DateField2[*].rawValue, "MM/DD/YYYY")
var answer = DateTo - DateFrom
Is there anything that I am missing?
Thanks in advance
if you want to have dates calculated in a multiple row Table, you should have a loop to get through each rows.. You can't use [*] in the Date2Num() function to get all values, even if you add the Sum() function it wont work..
Also, to use the [*] for reference, you always have to put it on the object which has multiple instances, not the field because you are not adding instances on the field..
So to be able to do what you are trying to do would look something like this:
Do the fields have to be in different column?
I have a subform that contains the both dates and would like to calculate inside the subform which is in one cell.
I tried it and the error I got was
So what I did was removed .rawValue. but the formula would still not work.
If there is a subform which holds the date fields, you must add it to the reference...
Table3.resolveNode(Concat("Subform1.Row1[", i, "].DateTimeField1")).rawValue;
This time I have added it in and I don't get an error message but the field won't calculate. It just shows it as 0 regardless the date entered. By the way, I had to remove the .rawValue because it gives me an error
This is what I have now
var DateFrom = 0
var DateTo = 0
for i = 0 upto Table3._Row1.count - 1 step 1 do
DateFrom = DateFrom + Date2Num(Table3.resolveNode(Concat("Subform1.Row1[", i, "].DateField1")), "MM/DD/YYYY")
DateTo = DateTo + Date2Num(Table3.resolveNode(Concat("Subform1.Row1[", i, "].DateField2")), "MM/DD/YYYY")
DateTo - DateFrom
I am thinking do you think there is something that I need to change in the field properties in order to get it working?
Thanks in advance.