Updating parent from child with new Calculated Expressions

773
3
03-28-2022 12:47 PM
DamonBreen
New Contributor III

Hi all,

With the new Calculated Expressions ability in field maps, I have some things that I have been wanting to try, so I've been getting into that today. However, I am no code-wizard, and don't really have the resources for help around me. 

I think I'm pretty close with what I am wanting to do, but keep getting an error. I have a map that contains mostly point features that represent real world attributes, and we have time based inspections that we have to complete on thsese point features. The inspections are represented by related tables that are distinct to each of the point features. The point features also have a completion status field, that after the end-users submit a new inspection, needs to be changed to reflect the level of completion. 

What I would like to do, is set up a calculated field in the completion status field, and have it look to the most recent inspection performed, and compare the date to a date window.  I have posted my sample code below. The relationship is: GlobalID ("Odorizer" Feature) = ODGlobal ("Odorizer Inspection" Table)

var global = $feature.GlobalID; //declare Global ID of current feature as a variable to reference for relationship
var tblreads = FeatureSetByName($map, "Odorizer Inspection"); //get all of the related features as a variable
var sql = "ODGlobal = '" + global + "'"; // set up a query string
var relRead = Filter(tblreads,sql); // filter list of related records by Global-Guid Relationship. This gets only related records pertaining to feature in question.
var cnt = Count(relRead); // counts number of related records related to particular feature
var begWindow = date(03,01,2022); //sets the beginning of the completion window
var endWindow = date(03,31,2022); //sets the end of the completion window
var LastDate = ""; // empty variable to be used to pull last reading date from most recent related record
Console(cnt);

var recentMostRead = Top(OrderBy(relRead, 'ObjectID'),1); //creates a new variable to capture the most recent related record by ObjectID

LastDate += recentMostRead.CurrentReadDate; //dump the reading date field from the last related record into the LastDate variable created earlier.


if(begWindow < LastDate < endWindow) { //compare the date from the recent most related record to the completion windows called out above.
    return "Completed, Not Approved";//If the record date is between the comparison dates, this should be the value in the field.
}
else {
    return "Not Completed";//If the record date is NOT between the comparison dates, this should be the value in the field.
}

Any help is appreciated! This will be applied to all of the rest of my point features in the map, so it will have a substantial impact I think.

3 Replies
DamonBreen
New Contributor III

So for an update on this, I was able to get it to work within the arcade editor in the smart form creator. I made a change to how I was pulling the reading date, as the error I was getting was related to not being able to pull "CurrentReadDate" from the feature set "recentMostRead". I instead did a for loop to iterate through the one record that was pulled, and I also had a formatting issue with my dates for the completion window. 

So now it works in the field maps editor, but it does not work in field maps on the iOS beta app. I'll post my code here and maybe someone can help me out. Is it possible that the calculated expressions don't work when going across a relationship?

var global = $feature.GlobalID; //declare Global ID of current feature as a variable to reference for relationship
var tblreads = FeatureSetByName($map, "Odorizer Inspection"); //get all of the related features as a variable
var sql = "ODGlobal = '" + global + "'"; // set up a query string
var relRead = Filter(tblreads,sql); // filter list of related records by Global-Guid Relationship. This gets only related records pertaining to feature in question.
var cnt = Count(relRead); // counts number of related records related to particular feature
var begWindow = Date(2022,02,01); //sets the beginning of the completion window
var endWindow = date(2022,02,31); //sets the end of the completion window
var LastDate = ""; // empty variable to be used to pull last reading date from most recent related record
Console(cnt);

var recentMostRead = Top(OrderBy(relRead, 'ObjectID'),1); //creates a new variable to capture the most recent related record by ObjectID

if (cnt > 0) {
for (var read in RecentMostRead) {
var RD = read.CurrentReadDate
LastDate += RD
LastDate = Date(LastDate)
}
} else {
return "Not Complete"
}

if (LastDate == "") {
return "Not Complete"
} else {


if(begWindow < LastDate < endWindow) { //compare the date from the recent most related record to the completion windows called out above.
return "Completed, Not Approved";//If the record date is between the comparison dates, this should be the value in the field.
}
else {
return "Not Completed";//If the record date is NOT between the comparison dates, this should be the value in the field.
}
}

 

Thanks,

Damon

by Anonymous User
Not applicable

I too was able to get it working (or some version of this code) in the FieldMaps editor, but it isn't automatically doing it on iOS. I have been able to get other relationship calculations to work on the child side, but not here. I'm interested if anyone else has tried this and got it to work.

0 Kudos
DamonBreen
New Contributor III

I was able to get it to work. Below is what I used for it.

var AllReads = featuresetbyname($map,"Odorizer Inspection");
var Global = $feature.GlobalID;
var sql = "ODGlobal = '" + Global + "'"
var RelatedReads = Filter(AllReads,sql);
var numrelreads = count(RelatedReads);
if(numrelreads == 0){
    return Null;
}else if(numrelreads != 0){
    var LastRelRead = First(OrderBy(RelatedReads,"CurrentReadDate DES"));
    var LastCompletionDate = LastRelRead.CurrentReadDate;
    var CurrentDate = Today();
    var age = datediff(CurrentDate,LastCompletionDate,'days')
    if(age > 15){
        return "Not Completed"
    }if(age <= 15){
        return "Completed, Not Approved"
    }
}