Instance name depending on how old it is

[AdrianPatrulescu]

Hi ,

I want to check on the fields only camera trap that was not verify for at least two weeks (14 days)

The survey was posted using survey123 connect desktop application

In settings - instance name i put : if(${EditDate} - today() >14*24*3600*1000, concat('<font color="blue"><i><b>', ${toponym}),concat('<font color="red"><i><b>', ${toponym}, '</b> '))

The error that I get is EditDate - is not a survey field (and is not, but on server also is registered the EditDate of each camera (installation, verification - multiple times, and uninstall camera))

The action made on the cameras are stored in a related table but the EditDate from main table is changing every time i make new record on the related table.

So in the list of records (inbox in survey 123) i need to know witch site (location of camera) are not visited over 14 days.

Thank you

Adrian

[Anonymous User]

Hi @AdrianPatrulescu,

In the instance name you can not reference the metadata field such as edit and creation date or user. You can only reference fields/questions that are in the survey XLSForm. Are you able to include a EditDate in your survey, even if it was hidden, so that it gets populated and updated when survey is updated, that way you can reference it in the instance name and use it to filter the Inbox.

Regards,

Phil.

[AdrianPatrulescu]

Hi Philip,
Thank you for your reply

I have few questions:

The survey combine 2 tables (main table - describing camera location and repeats table - describing camera actions (installation, verification's - multiple records, uninstallation))

• I can put a date in the repeats table (related table) but how can I get the newest date (because are multiple camera actions = multiple dates) from one field?
• The instance name can read related table?

On the inbox already existing a Modified date (see picture below) which is best for me to calculate the age of the last edit date and color differently the instance name title

• How can i get Modified date into instance name formula?

Thank you

Best regards

Adrian

[Anonymous User]

Hi @AdrianPatrulescu,

The modified date used in the Inbox list view is pulled directly from the feature layer and is the metadata field for date modified. This gets updated whenever any app updates or edits that feature. This is not part of the XLSForm and hence can not be used in the instance name, as it is not a referenced field (question name) in the XLSForm.

However you can work around this by creating an EditDate field in you survey in the parent layer and use a calculation to update it, based on the dates that you have in the repeat records. With the calculation you can find the max data (most recent) and update the EditDate with this value when it changes in the repeats.

If that doesn't work due to the way the dates and repeats are edited, you could also use a JS function to do the same query as the calculation, and return the max date from the repeats.

We have some new functionality coming in the 3.14 release in regards to calculationModes, which will allow you to set different modes for how and when calculations run, and if they constantly update when values change. This may also be helpful in your use case.

Expression engine improvements and calculation modes - ArcGIS Survey123 (esri.com)

If you are still have trouble getting this working, please share your XLSForm, and I can take a closer look and advise further.

Regards,

Phil.

[AdrianPatrulescu]

Thank you

I have attached my XLSForm

We can use DateTimeCam field witch already exist in the repeat table or can create an EditDate field in main table

Thank you 

[Anonymous User · Accepted solution]

Hi @AdrianPatrulescu,

Sorry for the delay, finally got some time to take a look at your survey. I found a few issues which I have updated in a cut down version of your survey, and fixed the instance name calculation.

I added a DateTimeCamUpdate field to the parent (made it text and hidden appearance) and used a calculation to get the max() value from the repeat DateTimeCam date field. This will auto update with the max() date value every time a date changes in any of the repeats. I used text fields with the hidden appearance, but you can also use the hidden question type, they are similar, however the hidden appearance is more flexible as it is a real input field in the form, the hidden field type is not. You could also use date fields, but up to you. Note sure if it matters if they are real dates, or just values, as you can also format them using date format functions. If you just use hidden or text fields, in the database they are not true date values, just numeric strings. See attached xlsx file which is my cut down working version.

In the instance name you can then point at this DateTimeCamUpdate field and do the if() calculation and html formatting based on the different date values. Your html was missing the closing syntax for font, italics and bold. I saw in above comments you used some of these, but in your xlsxform the instance name was different, you must have removed or updated it before sending to me. Therefore I just made mine own instance name that was similar to test it works, but update it as needed:

if(today() - ${DateTimeCamUpdate} > 1209600000,concat('<font color="red"><i><b>',${toponym},'</b></i></font>'),concat('<font color="blue"><i><b>',${toponym},'</b></i></font>'))

The results in the Sent, Drafts, Inbox etc are colored either red or blue depending on the max date value in the repeats:

Hope this helps.

Regards,

Phil.

[AdrianPatrulescu]

Hi Philip

Thank you for the response

It work perfectly for me

Best regards

Adrian

[MichelleWilliamsERM]

That's what I needed, thank you!

I had an older version that wasn't working for me anymore.

[MikeSweeney]

What does the number 1209600000 represent and how did you come up with the value?  I think it somehow represents 14 days, but I don't know what system it is in?

Thank you!

if(today() - ${DateTimeCamUpdate} > 1209600000,concat('<font color="red"><i><b>',${toponym},'</b></i></font>'),concat('<font color="blue"><i><b>',${toponym},'</b></i></font>'))

[AdrianPatrulescu]

14*24*60*60*1000

https://community.esri.com/t5/arcgis-survey123-questions/epoch-time-millisecond/td-p/834984

Epoch time with miliseconds