Instance name depending on how old it is

329
6
Jump to solution
01-07-2022 06:12 AM
AdrianPatrulescu
New Contributor III

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

0 Kudos
1 Solution

Accepted Solutions
Philip-Wilson
Esri Notable Contributor

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.

PhilipWilson_0-1642670028482.png

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:

PhilipWilson_1-1642670356375.png

Hope this helps.

Regards,

Phil.

 

View solution in original post

6 Replies
Philip-Wilson
Esri Notable Contributor

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.

0 Kudos
AdrianPatrulescu
New Contributor III

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?

 

AdrianPatrulescu_0-1641981319845.png

Thank you

Best regards

Adrian

 

0 Kudos
Philip-Wilson
Esri Notable Contributor

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.

0 Kudos
AdrianPatrulescu
New Contributor III

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 

0 Kudos
Philip-Wilson
Esri Notable Contributor

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.

PhilipWilson_0-1642670028482.png

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:

PhilipWilson_1-1642670356375.png

Hope this helps.

Regards,

Phil.

 

AdrianPatrulescu
New Contributor III

Hi Philip

Thank you for the response

It work perfectly for me

Best regards

Adrian