Pull Data to Calculate Future Date

1707
6
Jump to solution
09-30-2021 10:04 AM
JoshuaFergusonPCW
New Contributor II

I have a Survey123 form that allows field staff to collect information regarding equipment maintenance. The maintenance occurs on regular intervals that vary based on the maintenance type. In the form I'm using a 'Date of Next Maintenance' which is currently populated manually by field staff. The values that are populated are used to send a notification of an future maintenance action that will need to be performed via a web hook. I would like to automate the process of populating the future date in the 'Date of Next Maintenance' field. 

I'm able to 'hardcode' the future date value in the survey using a calculation like now()+7*24*60*60*100. I'm also able to pull the same calculation from my CSV as a note into the survey. However when I change the field value from note to date, the calculation won't update the field with the calculated date. So to recap:

Date calculation works with the date field when put directly in the Form and not using pulldata. 

Date calculation can be populated as a string or text using a field of type note when using pulldata.

Calculation is not updating a field of type 'date' when using pulldata. 

I'm using the most current version of the Survey 123 field app. I've attached images of my pulldata form and my Survey 123 form. 

 

0 Kudos
1 Solution

Accepted Solutions
DougBrowning
MVP Esteemed Contributor

It is coming in as a string prob.  Try using this function.

date-time(question, expression, or string)  Converts a number or string to a date object. 

date-time('2017-05-28T04:39:02+10:00')

Your pull data looks like formulas.  I do not think you can pull text them run that as a formula.  Not sure why you have the math in there I would just have a number like 7 for 7 days.  Then use pulldata to get the number, wrap that in int() also then add that to now in the field calc.

Hope that helps

View solution in original post

6 Replies
DougBrowning
MVP Esteemed Contributor

It is coming in as a string prob.  Try using this function.

date-time(question, expression, or string)  Converts a number or string to a date object. 

date-time('2017-05-28T04:39:02+10:00')

Your pull data looks like formulas.  I do not think you can pull text them run that as a formula.  Not sure why you have the math in there I would just have a number like 7 for 7 days.  Then use pulldata to get the number, wrap that in int() also then add that to now in the field calc.

Hope that helps

AlfredBaldenweck
MVP Regular Contributor

Pulldata() will just read the string of the formula, yeah.

I'm glad I came across this; I'm happy I'm not the only one who wants to use this function like this.

0 Kudos
LeonardBarnhill
Occasional Contributor

I have a slightly different approach that may not be helpful as it does not use Pulldata request.

I created a select_one field with the name MONITOR that reads a short list from the choices tab with the list_name monitor and name choices of 1YR, 3YR, 5YR, and 10YR

I next have a calculation row with the name nextcalc that has the formula if(${MONITOR}='1YR',date(decimal-date-time(now())+365),if(${MONITOR}='3YR',date(decimal-date-time(now())+1095),if(${MONITOR}='5YR',date(decimal-date-time(now())+1826),date(decimal-date-time(now())+3652))))

I finally have a text field named NEXTINPS to display the Next Inspection Date that is ReadOnly with the formula format-date(${nextcalc}, '%b %d, %Y')  

The Previous Inspection Date shown in the screenshot has nothing to do with the Next inspection Date other than to inform the user of the last inspection to help the user determine if the next inspection should be the same time span or if it can be different in the future

I hope this is useful. 

JoshuaFergusonPCW
New Contributor II

Big thanks to both Leonard and Doug. I was able to use information from both post to finally get things running correctly.

The new formula I'm running in the calculation field is 

date(decimal-date-time(now()+int(${DNMCalc})*24*60*60*1000))

The only thing that pulldata form now populates is the int() portion which changes based on the maintenance interval the user provides. I've attached updated screen shots. 

DougBrowning
MVP Esteemed Contributor

If you use date-time instead of decimal-date-time you can skip the *24*60*60*1000 part since it uses days then.  Would be cleaner.

date(date-time(now()+int(${DNMCalc})))

0 Kudos
JoshuaFergusonPCW
New Contributor II

Thanks Doug!