Jump to content

Use different value lists on same field


carlosnorvik
 Share

This topic is 2366 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

"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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.   :-)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by LaRetta
Link to comment
Share on other sites

This topic is 2366 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.