Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

subtract dates and have a dropdown box that would reference to it

MGonghai
Level 1
Level 1

Hello,

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.

6 Replies
Magus069
Level 10
Level 10

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...

MGonghai
Level 1
Level 1

Hello Magus069

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

Magus069
Level 10
Level 10

Hi again,

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:

MGonghai
Level 1
Level 1

Hello again,

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 ls error.pngUntitled111.png

So what I did was removed .rawValue. but the formula would still not work.

Any suggestions?

Thanks

Magus069
Level 10
Level 10

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;

MGonghai
Level 1
Level 1

Hello Magus069,

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")

endfor

DateTo - DateFrom

order.pngsom.png

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.