Use different value lists on same field

[carlosnorvik]

Hi Everybody

I wish you all a very prosperous 2016 and may all your wishes come true.

I have been using some of my holiday time to update my FM solutions, so far I have managed most of what I wanted to do and learned a couple of tricks on the way.

Now I have found a roadblock, using different value lists on the same field. I have many value lists based on relationships and they work fine, but in this case the situation is:

a) It is a checkbook solution, if there is a relationship it displays certain values (List of debtors/creditors in one field, invoice numbers with balances > 0 in the next and Invoice value on the next).

b ) However payments and deposits come not only from purchases/sales of trade goods (relationships and resulting value lists come from the purchasing/invoicing solutions), I need to use a completely different value list if the operation is not related to trade (for example salaries, office consumables, sales of old and depreciated capital equipment, etc.).

If somebody know how to do it, I will really appreciate some insight.

 

Thanks and enjoy the party tonight.

Carlos

 

[LaRetta]

Hi Carlos,

You can use a dynamic value list, attached to a field which is applied at the moment the control is popped via script trigger but I could not make more detailed suggestion without a better idea of your structure.  I suggest that you put together a file showing just the basic relationships and fields involved in those few tables.  If folks here have a specific example, it will be easier for them to assist you on this one.

Your description might indicate a wrong turn in structure so providing a sample file will help us see it clearly.

[bruceR]

"if there is a relationship it displays certain values"

How is the relation established? Which field establishes this relation? Agree that a copy, clone, or example file would be helpful.

[carlosnorvik]

Hi Bruce & LaRetta:

I don't have a problem with the value list itself, it works as intended. What I wanted to do is to use a completely different value list if there wew no related records for populating the main one.

I did it less "elegant" by displaying another tab.

Thanks for replying to my post! :-)

 

Carlos

[bruceR]

You could always try answering the question.

[LaRetta]

Hi Carlos,

I didn't suggest you had trouble with your single value list but rather that you can switch to dynamic value list to hold two different value lists depending upon certain criteria.  There are other methods as well and now we'll never know which would be best for you to use because you are not giving us a chance to offer suggestions.

Regardless, I am pleased you resolved it.   :-)

[carlosnorvik]

Hi LaRetta and Bruce:

Thanks for your replies.

I will upload a simplified sample so you can take a look, it will take a few days because I'll translate it from Spanish and simplify it to show only this issue, otherwise it can be overwhelming to figure out, it has slowly grown into a spiderweb of relationships, both within the fp7 itself and to separate fp7's used in common with other fp7's  ('shared fp7's are for contacts, exchange rates, Incoterms, etc.).

 

Thanks again

Carlos

[comment]

It's not possible to change the value list attached to a field instance on the layout. Perhaps you could use another instance of the same field - or rethink your design. In general, a field's values should all come from a well-defined domain - and therefore a single value list should be sufficient to list them all.

[LaRetta]

2 hours ago, comment said:

It's not possible to change the value list attached to a field instance on the layout. 

I was considering a method learned from you long, long ago but I lacked specific knowledge of the setup to provide a good solution or demo file which is why I asked Carlos for more information.  :-)

Hi Carlos,

I could see various value lists pulling together and providing 'conditional' value list for checkbook, transactions table or General Journal depending upon the category or type.  By using dynamic value lists, we can 'pop' different value lists on the same field.

In this example file (fp7 format), I used one real value list (from Invoices) and one hard-coded multiline but it can be any combination of multiple value lists or multiline lists which will change depending upon the Case() statement in the script parameter.  Ideally, you wouldn't hard-code the multiline in the script parameter but it shows what is possible.  In more recent versions of FM, we can use ExecuteSQL() as well ... options abound.  

What you will notice if you open the file:  

The first record has a valid relationship to Invoices so the Type field uses the Invoice Types value list when you select the drop-down arrow.  The second record lacks that relationship so it uses the multiline specified in the script parameter of the script trigger attached to the field.  There are some great example files here on the Forums, put out by Comment and John Ahn but my links appear to be broken and I got tired hunting for them, sorry - much quicker to just put together a demo.

Comment, is this the best solution for Carlos?  I have no idea ... but it certainly is possible, thanks to what I picked up from you on FileMaker Forums clear back in 2007 (I think) when you mentioned it would work to use unstored value lists.

14 minutes ago, LaRetta said:

Ideally, you wouldn't hard-code the multiline in the script parameter but it shows what is possible.

Actually, I take this back.  Many times we insert text as a script parameter for running a script ... "Open", "Closed" and any number of 'hard-codes' so this is not an evil thing at all.  But if you can use value lists based upon field data, so much the better.  I've used hard-coded multiline like this based upon special circumstances.

DynamicValueList.fp7

Edited January 5, 2016 by LaRetta