using find (?) in a Calculation?

[Rancher]

Hello, I am pretty new to FM and dealing with Calculation fields so bear with me for the flat-out ignorance that follows.

I would like to have a Calculation field in Table1 that conculcates data from a field in related Table2 but only if a condition in another field in Table2 is matched. Here is a vastly simplified example:

Table2 (where the data is stored) has COUNTRY and STATE fields, with each STATE being a new Record. 

In Table1 I want to have a calculation field that conculcates all of the STATE fields but only if COUNTRY = USA (thus excluding UK or France).

Currently, my calculation is  

If ( TABLE2::COUNTRY ; "USA" ; Substitute ( List ( TABLE2::STATE) ; ¶ ; "; " ) )

While this does not result in an error, it pulls in all STATE fields (so even those that are not USA) so long as there is at least one from USA. What I cannot figure out is how to limit it to just USA. Obviously an initial Find command would help solve that, but I don't seem to be able to do that.

Also, assuming that I can get STATE in properly, is there a way to sort it as well?

Thank you!

[comment]

2 hours ago, Rancher said:

Currently, my calculation is  

If ( TABLE2::COUNTRY ; "USA" ; Substitute ( List ( TABLE2::STATE) ; ¶ ; "; " ) )

While this does not result in an error, it pulls in all STATE fields (so even those that are not USA) so long as there is at least one from USA.

I think you meant:

If ( TABLE2::COUNTRY = "USA" ; Substitute ( List ( TABLE2::STATE ) ; ¶ ; "; " ) )

That would list all related STATE values as long as the first related record is from USA.

To get the result you want, you need to define a calculation field in Table2 = 

If ( COUNTRY = "USA" ; STATE )

then apply the List() function in Table1 to this field. 

Alternatively, you could use a custom function or the While() function to loop over the related records and add the STATE value to a list if the COUNTRY is "USA". Filemaker has no built-in functions similar to Excel's COUNTIF and SUMIF that would allow you to add a condition to an aggregate function.

Yet another option is to add a relationship to a new occurrence of Table2 and add STATE to the match fields, so that only USA records would be related.

 

Edited June 18, 2020 by comment

[Fitch]

Or you could use ExecuteSQL. Or just hard-code the list of states in your script, or in a value list, or a custom function.

[comment]

2 hours ago, Fitch said:

Or you could use ExecuteSQL

I would not use ExecuteSQL in an unstored calculation.

2 hours ago, Fitch said:

Or just hard-code the list of states in your script, or in a value list, or a custom function.

That might be a good solution in this case - and it could be implemented right in the calculation itself, with no need for a script or a value list or a custom function:

Substitute ( FilterValues ( List ( TABLE2::STATE) ; "Alabama¶Alaska¶Arizona¶Arkansas¶California¶Colorado¶Connecticut¶Delaware¶Florida¶Georgia¶Hawaii¶Idaho¶Illinois¶Indiana¶Iowa¶Kansas¶Kentucky¶Louisiana¶Maine¶Maryland¶Massachusetts¶Michigan¶Minnesota¶Mississippi¶Missouri¶Montana¶Nebraska¶Nevada¶New Hampshire¶New Jersey¶New Mexico¶New York¶North Carolina¶North Dakota¶Ohio¶Oklahoma¶Oregon¶Pennsylvania¶Rhode Island¶South Carolina¶South Dakota¶Tennessee¶Texas¶Utah¶Vermont¶Virginia¶Washington¶West Virginia¶Wisconsin¶Wyoming" ) ; ¶ ; "; " ) )

(add D.C. and territories as needed).

--
P.S. If you exchange the parameters of the FilterValues() function, you will get a sorted list as a bonus.

 

Edited June 19, 2020 by comment

[Fitch]

Agree 100% about ExecuteSQL -- only use that in scripts, not field definitions.

Not sure what's the point of Substitute ( FilterValues ( List ( TABLE2::STATE)  ; [states]

Why not just the text?

If ( COUNTRY = "USA" ; "Alabama; Alaska; ... etc" )

[comment]

18 minutes ago, Fitch said:

Not sure what's the point of Substitute ( FilterValues ( List ( TABLE2::STATE)  ; [states]

I don't think we are on the same page here. The purpose - at least as I understand it - is to get a list of all related STATE values, except values where COUNTRY is not USA. Not sure how your suggestion would get us closer to that.

 

 

[Fitch]

conculcate (obsolete): to tread or trample underfoot

You're probably right. I thought they wanted a list of all states. I've re-read the post but it's still not clear to me.