Creating a value list from two fields in the same table

[jjjjp]

I want to do something seemingly straightforward, but it seems very hard to accomplish. I have two global text fields that a user sets in a table TGlobal. At some point, I want the user to choose between the text values in the two fields, FieldOption1 and FieldOption2, which represent different options that a user can take at some point in the workflow. So ideally I'd like to create a value list, which can be used to present a user with the two clear choices in an elegant layout that contains a question and a radio button.

So here's what I have tried to do.

1. I created a table, VList, with two fields (ValueOptionID and ValueOption) and two records. The first record has ValueOptionID 1, the second ValueOption 2. I set up a calculation field for ValueOption. The calculation is a Case statement with the following straightforward logic: if ValueOptionID is 1, ValueOption is TGlobal::FieldOption1, and if ValueOptionID is 2, ValueOption is TGlobal::FieldOption2.

Then I tried to create a Value List using values from a field. I used values from the field ValueOptionID and displayed them from the field ValueOption. In principle, the user sees understandable text, but the script knows which field the user has chosen (depending on whether the result is 1 or 2) and can proceed accordingly.

Except that Value Lists can't use calculation fields, at least for the second value.

2. So I tried making ValueOption a text field rather than a calculation, but I auto-entered a calculated value using the exact same case statement as above. The only problem is that if the user were to change the value of TGlobal:FieldOption1 or TGlobal:FieldOption2, the corresponding value of VList::ValueOption wouldn't change for either record because no other field in the two records has changed. 

Is there a way to make this work? Am I making it too complicated? Or do I have to make it more complicated still? I have been away from coding in FM for some time and so am rusty.

(I know that some responders prefer a concrete description of the problem, and I can supply one if necessary. But I have kept it abstract for now because the concrete details will complicate the question considerably and seem to me entirely extraneous.)

[Ocean West]

Does it have to be a value list?

How many "options" do you anticipate?

Other options would be to use a card window and a portal or even list view and allow user to search - or you could constrain search when you open card window

https://share.getcloudapp.com/KouBP4pe

 

 

[comment]

Can you explain the purpose of this exercise? I lost you right at the beginning, when you said:

1 hour ago, jjjjp said:

I have two global text fields that a user sets in a table TGlobal.

When exactly would the user do that? Are you aware that in a hosted solution global fields are reset to their initial values at the beginning of each session?

Usually, to allow users to select from two (or more) different sets of options, you would use a conditional value list. In a nutshell, you would have an Options table like:

Colors	Red
Colors	Green
Colors	Blue
Sizes	Small
Sizes	Medium
Sizes	Large
Sizes	XXL

a global field for the user to select either "Colors" or "Sizes" (which also serves as the match field to the Options table) and a value list that shows related values only.

 

[jjjjp]

Thanks, both.

Consultant, I can see why you're confused. I can see that I abstracted away important information for understanding the context.

The global fields in question are in a table, ProgramsGlobal, whose fields exactly mirror a table of non-global fields called Programs.  There are different programs in the database corresponding to different coherent sets of workshop offerings. At the beginning of a session, the user chooses a program. The fields in Programs in effect, represent key settings for a program that don't get changed frequently. Each record in Programs contains the settings for a specific program. When a user (usually someone with administrative privileges) changes a field in Programs, a script runs that automatically copies all of the values from the current record for Programs over to ProgramsGlobal. Scripts often rely on these settings. The Shadow copy of Programs in ProgramsGlobal is a coding convenience. The one, practically negligible, drawback is that if user 2 changes one of the parameters while user 1 is in a session, user 1 will still be relying on the old value of the parameter. The newer values will, however, appear if user 1 opens the layout that will allow him or her to modify the settings.

I'm sure this practice is not at all uncommon.

In this case, the two settings I want the user to be able to choose between represent the two possible types of schedule of workshops that in this particular use case the user will convert into HTML code. The user may then copy the code to a web site that publicizes the workshops. The parameter in this case helps determine the nature of text at the head of the HTML code.

I think I've come up with a workable, if slightly inelegant, solution that allows me to use a value list that will appear to the user in a "dialogue" as a radio button set. In answer to your question, Ocean West: since this dialogue will appear to the user in a sequence of similar dialogue, all in the same format, I was prepared to do what I needed to do to make this format work. (I tried using a Custom Dialogue, but it looked awful.)

To this end, I created a simple table called HTMLHeadingDescriptions, with three fields: a session id field corresponding to a unique id for the current session, a heading descriptor id (with possible values 1 or 2), and a heading descriptor. When the script was ready to allow the user to choose between two of the settings in ProgramsGlobal (text descriptors of the schedule type), the script creates two new and temporary records:

session id = <current session id>, heading descriptor id =1,  heading descriptor = value of heading descriptor 1 in ProgramsGlobal

session id = <current session id>, heading descriptor id =2,  heading descriptor = value of heading descriptor 2 in ProgramsGlobal

I created a value list from the field heading descriptor id, which also displays the field heading descriptor. The value list includes only values related to the session id.

After the user chooses, the two temporary records get deleted.

This seems to work fine. I was hoping there was a simpler way of doing this, but I decided that the simpler way probably doesn't exist if I am to insist on a radio button dialogue.

If I missed an easier solution, I am of course interested. But you can consider the problem solved.