topic Re: Autopopulating fields from a related table using Arcade in ArcGIS Field Maps Questions

Autopopulating fields from a related table using Arcade

DaveK — Tue, 11 Apr 2023 14:39:21 GMT

Hello!

I'm creating a Field Maps collection form which includes a parent point layer as well as a related table. The parent point layer contains a UniqueID field which is a domain that the user will select. Is it possible to auto populate the remaining fields in the parent table from the information in the related table based on the UniqueID chosen? I think the "Fetch an attribute form a related table" from this page - https://www.esri.com/arcgis-blog/products/field-maps/field-mobility/common-calculated-expressions-for-arcgis-field-maps/ is the closest to what I'm looking for. Instead of populating the related table from the parent, I would like to auto populate the parent layer from the related table. Any help is appreciated.

Thanks.

Re: Autopopulating fields from a related table using Arcade

DougBrowning — Tue, 11 Apr 2023 15:08:38 GMT

What you want is the FeatureSet function like this

var tbl = FeatureSetByRelationshipName($feature, "Points", ['DesignLat'], false);
return First(tbl).DesignLat

Hope that helps

Re: Autopopulating fields from a related table using Arcade

RhettZufelt — Tue, 11 Apr 2023 15:13:40 GMT

Depending on how complex it is, another option would be to just calculate the values in the form for the remaining fields based on what is chosen for UniqueID.

Re: Autopopulating fields from a related table using Arcade

DaveK — Tue, 11 Apr 2023 15:43:11 GMT

Thanks @DougBrowning,

This is what I have so far. I ended up using FeatureSetByName.

So far this works and it auto populates the information from the related table Field1 into Field1 in the parent layer. I cant figure out how to return other fields at the same time. For example, I have the Field1, Field2, Field3, etc, that I would like to auto populate into the parent layer. Do you have any ideas as to how I can include these as well?

Thanks.

Re: Autopopulating fields from a related table using Arcade

DougBrowning — Tue, 11 Apr 2023 15:53:49 GMT

No you would need to do this for each field. I have upwards of 32 to populate all of mine. Well you could return them but you cannot store all that in one field so not sure what you mean.

Add the specific field you want and add the false part to not return the geometry and it will be faster like in my example. I am not sure why you are hard coding in the 1111 thing vs just calling by name but I guess you have a reason.

Re: Autopopulating fields from a related table using Arcade

KenBuja — Tue, 11 Apr 2023 15:56:47 GMT

You can simplify this by using the UniqueID directly in your filter

var related = FeatureSetByName($map, "pointlayer - points"); var uid = $feature.uniqueid; var relatedRecord = null; relatedRecord = First(Filter(related, "uniqueid = @uid")); if (relatedRecord != null) return relatedRecord['Field1']; return null;

Re: Autopopulating fields from a related table using Arcade

DaveK — Tue, 11 Apr 2023 16:53:32 GMT

Thanks @KenBuja this helped!

Re: Autopopulating fields from a related table using Arcade

ErikWalling — Wed, 22 Nov 2023 14:41:46 GMT

Hi Doug,

I have a very similar issue but just different enough so that the solution provided isn't quite the fix I need. I am trying to calculate all the values in a field based on the values from another field in a related table. Here's the set-up. The parent table Sewer_Lines contains PipeLengths for all of our sewer lines. We have a related table for flushing these lines that records each time the line is cleaned which also contains PipeLengh. Since we've already been keeping a flushing record for some time now, I'd like to have the PipeLength(s) from Sewer_Lines calculated into PipeLength(s) in the Sewer_Lines_Flushings table. I am able to get the values this way

var flushlen = FeatureSetByName($datastore, "Sewer_Lines", ['PipeLength'])
flushlen

But this also returns the OBJECTID field, not just PipeLength. I've also tried this

var flushlen = FeatureSetByName($datastore,"Sewer_Lines", ['PipeLength'])
var feature =First(flushlen)
if (!IsEmpty(feature)) {
return feature['PipeLength']
} else {
return 0
}

But it returns just the first record in the parent table and not all of the records. Is there a way to update all of the records in Sewer_Lines_Flushings.PipeLength based on Sewer_Lines.PipeLength?

Re: Autopopulating fields from a related table using Arcade

DougBrowning — Wed, 22 Nov 2023 14:57:38 GMT

Not sure what you mean by all the values into a field. You can't have multiple values in one field.

If you mean just create a string of values from the repeats then you do that by making a string in a loop like shown in this post.

https://community.esri.com/t5/arcgis-pro-questions/arcade-array-output-from-related-table-help/td-p/1139909

If you just want to display the repeat values then the new map viewer can do this for up to 10 records.

I think there is a way to display a table also but I forget.

Hope that helps

Re: Autopopulating fields from a related table using Arcade

ErikWalling — Tue, 27 Feb 2024 13:22:02 GMT

Hi again,

I am trying to get this to work and have tried many variations without success, do you have any suggestions?

var x = ($feature.Scum + $feature.Sludge)

var y = FeatureSetByRelationshipName($feature,"MPCVRS", ['LiquidDepth'], False)

var z =First(y)

if (!IsEmpty(z)) {

return 1

} else {

return (x / z['LiquidDepth'])

}

I am trying this through a form in ArcGIS online and then going to FieldMaps to test. I am trying to calculate capacity (sludge depth + scum depth / liquid depth). My result for var x is what I expect but z seems to want to bring along object ID and not just the value for liquid depth. The other potential issue is scum and sludge depth is being provided by user input in the form but the form seems to run the calculation as soon as the form is opened rather than after user input.... Is there a way to change that or some other work around you're aware of? I should note that capacity does not need to be calculated in the form necessarily. The other way I could do this is in the main table and possibly in the pop-up but I am not sure if that type of expression would be allowed in the pop-up or if that's more for formatting text....

Re: Autopopulating fields from a related table using Arcade

DougBrowning — Mon, 04 Mar 2024 17:23:03 GMT

I your First you need to give a field name. Like this

var tbl = FeatureSetByRelationshipName($feature, "Points", ['DesignLat'], false);
return First(tbl).DesignLat

Also First will fail if it is null so if I remember right you need to something like this.

var x = ($feature.Scum + $feature.Sludge)

var y = FeatureSetByRelationshipName($feature,"MPCVRS", ['LiquidDepth'], False)

if (Count(y) > 0) {

return x / First(y).LiquidDepth

}

else {

return 1

}

Syntax may be off but you get the idea I hope.

Re: Autopopulating fields from a related table using Arcade

ErikWalling — Thu, 21 Mar 2024 16:41:11 GMT

Thank you for the insights! I think I finally beat this into submission. For one thing I had the wrong data type for calculating a decimal number so that definitely helped but this is what I ended up with.

var a = $feature.Scum

var b = $feature.Sludge

var c = $feature.LiquidDepth

var d = ((a + b) / c) * 100

IIf(c == 0, 0, d)

Re: Autopopulating fields from a related table using Arcade

DougBrowning — Thu, 21 Mar 2024 16:50:44 GMT

Pretty sure this would crash on var d if c =0 since you are checking it after the fact.

I would do more like

iif ($feature.LiquidDepth == 0, 0, (($feature.Scum + $feature.Sludge) / $feature.LiquidDepth) * 100)

Re: Autopopulating fields from a related table using Arcade

ErikWalling — Mon, 25 Mar 2024 11:33:17 GMT

Surprisingly this actually works as is. The variable "d" does not evaluate to 'true' unless variable "c" is not equal to zero. I kept getting a result of infinity which I believe was due to the calculation trying to divide by zero.

In the form liquid depth is pulled from the main table using:

var x = FeatureSetByRelationshipName($feature,"MPCVRS", ['LiquidDepth'])

var y =First(x)

if (!IsEmpty(y)) {

return y['LiquidDepth']

} else {

return null

}

Capacity is then calculated based on user entries for Scum and Sludge using:

var a = $feature.Scum

var b = $feature.Sludge

var c = $feature.LiquidDepth

var d = ((a + b) / c) * 100

IIf(c == 0, 0, d)

I then used:

var a = $feature.CalcCap

IIf(a > 33.0, "YES", "NO")

to determine if the tank needs to be pumped or not returning domain values "YES" or "NO"

I've successfully tested this multiple times through field maps.

Re: Autopopulating fields from a related table using Arcade

DougBrowning — Mon, 25 Mar 2024 13:53:01 GMT

It may be trying to help you by working but I would not do it that way. Never want code that divides by 0. Its as easy fix so why not.

If I remember right var y =First(x) will also fail if x is empty You need to check x for being empty first.

var x = FeatureSetByRelationshipName($feature,"MPCVRS", ['LiquidDepth'], false); //faster to not return geo

if (!IsEmpty(x)) {

return First(x)['LiquidDepth'] // or First(x).LiquidDepth

} else {

return null

}

Re: Autopopulating fields from a related table using Arcade

Krocha — Wed, 13 Nov 2024 17:36:43 GMT

Hello i need help understanding this arcade expression. On the first line "pointlayer" would be the name of the parent layer? Also "points" where is that name coming from?

Re: Autopopulating fields from a related table using Arcade

Krocha — Wed, 13 Nov 2024 17:39:17 GMT

Are you doing this expression in field maps designer?

Re: Autopopulating fields from a related table using Arcade

DougBrowning — Thu, 14 Nov 2024 14:39:45 GMT

ByRelationship should be fixed now so you can use that.

FeatureSetByRelationshipName($feature, "Name of the Relationship class", ['list of fields'], false);

(the last false is to not return geometry to speed it up.)

Re: Autopopulating fields from a related table using Arcade

EricaNova — Thu, 24 Apr 2025 18:41:05 GMT

I've literally been looking for hours and this is the only solution that worked for me.