Value List Based on Field Content Calculation?

[Tony Diaz]

I'm trying to make a value based on the content of JSON_cListKeys in this specific record only:

The fields involved:

 

 

I get the "This Value List Won't Work... based on a calculation" message. So I tried to see if looking up the content or calculating the content and using that instead.. um.. no, it's still a calculation.

 

I Tried changing to a calculated text field ... I get no error from the value list creator, but I still don't get actual values in the list.

In order to only get the values from the current record, I created a self join [Items <--> Items_Current_Record] and linked it to itself by the unique ID, and I also tried the JSON_cListValues field for the pair as well. (At the bottom of the screenshot)

 

 

I've found a similar thread but the data it's working with is numbers and increments. It's building that list by multiplication of intervals.

I have a feeling I need to either do something to actually get the $result of the calculation to be text.
The self join, if I understand it right, should limit my results to just that one record at least.

 

Sandbox.fmp12

 

 

Sandbox.fmp12.zip Sandbox.fmp12

Edited July 4, 2020 by Tony Diaz

[comment]

Your basic premise is flawed. The error message you see does NOT say anything about calculation. It says that the field cannot be indexed. It cannot be indexed, because the calculation is unstored.

If you want your value list to show values from the current record only, then define it to use values from the Items_CurrentRecord::JSON_cListKeys0 field, show only related values starting from Items. And you can get rid of the JSON_cListKeys_Values0 field, it serves no purpose that I can see.

 

[Tony Diaz]

Hmmm...  that JSON_cListKeys_Values0 field was because for whatever reason, I kept getting that same error message when I was trying to define the value list, so I thought maybe that it was trying to parse the formula calculation text itself, and not the result.

 

I had the right thought then, at least. The values on the self join table only and the field content is the calculation result. I must have been selecting something wrong in the beginning, before I added that .._Values0 field. 'cause, yes, it's doing nothing.

It's working now. Whatever?!?! I swear this is what I was doing before and getting that error.

...at least that was what I was thinking needed to be done, obviously I must have been picking something else.
Ugh.

Thanks

[Rich S]

I have a similar problem with the same premise--a value list based on a calculation but not using JSON--I have three fields: EmployeeName, Mentor, and Status. For the Mentor field, I want a value list (drawn from names in the EmployeeName field) where if the value, Active, is in the Status field then that EmployeeName becomes one of the values in Mentor's value list.

I tried using the FilterValues command but either I'm using the wrong calculation or it's not the appropriate tool for the task at hand. I also thought of using a TO and linking the Status fields together so I could set up a related field as a value list but I can't figure out how to filter--using Active--through a relationship.

TIA for your help!

Edited February 4, 2021 by WF7A

[comment]

If you want to use a relationship to filter the value list, then you will need a field that contains the text constant "Active" as the match field opposite the Status field. Then only records that contain the value "Active" in the Status field will be related.

Alternatively, you could use a calculation field that returns a value (EmployeeName or - preferably - EmployeeID) when the Status field contains the value "Active", and base the value list on this field. 

--
P.S. This has very little in common with the original question.

 

Edited February 4, 2021 by comment

[Rich S]

Sorry--I meant thread title instead of question. 

[Rich S]

(I didn't know whether to start a new thread or continue on here, so I'll leave it at your discretion.)

 

Well, I tried and couldn't get it to work...a new wrinkle being that the value list will be using a calculated field (c_FullName) as the source of values so indexing is a problem. That, and I just couldn't get the value list to "focus" on Active-only records--if you select an Inactive record then the value list would show Inactive record values instead of only Active record values. *sigh*

test.fmp12

Edited February 4, 2021 by WF7A

[comment]

See if the attached helps. Note that this is not a good solution, because the Mentor field stores a copy of the selected mentor's name. If the mentor's name is modified in its original record, you will get an update anomaly.

 

23 minutes ago, WF7A said:

a new wrinkle being that the value list will be using a calculated field (c_FullName) as the source of values so indexing is a problem.

The calculation can and should be stored, so there is no problem with indexing it. 

 

test2.fmp12

[Rich S]

Thank you! (Of course) I didn't think of that. Is there a better approach to this than what I came up with or should I include an error-checking script that's run via.a script trigger tied to the Mentor field so that if there's a name change the user is alerted to that fact?

[comment]

The standard solution is to store the selected mentor's unique ID, and display their name directly from their record using (another) relationship matching on the IDs.

Alternatively, you can format the field as popup menu so that it displays the name even without the extra relationship.

 

 

Edited February 4, 2021 by comment