Hi,

I've searched the various post on dynamic value lists and the answer to what I'm looking for isn't quite there...close, not quite.

I am trying to populate a value list with data from a table. And I need this to display on a invoice type page. So, the page shows records from Invoice Table, and I need to display two dynamic value lists from Products Table. The first dropdown list is called Company...upon choosing a certain company from the list I need the second dropdown to display items associated with that specific company...upon choosing the specific item the price of that item is now displayed.

I have tried various self-joins, but i must be missing something. I also tried to place everything with a portal...which seems to work somewhat, but won't allow me to start with a blank record because an error message comes up after I try to use the first dropdown.

Thank you for any help you can give!!

rez

Why you dont want to use "Only related values" option in Value List crating. Now I tried and it works as you want.

Go here: http://www.fmforums.com/threads/showflat...true#Post138775

Thanks for your help, that put me on the right track!

Another possibility for creating a value list is this

Create a COMPANY file

-Create x amount of ITEMNAMEFIELDs

-Create a field COMPANYNAME

Create a TEMPFILE.

-Create a field for each ITEMNAMEFIELD in the COMPANY file. (if there are 10 ITEMNAMEFIELDs in the COMPANY file, create 10 ITEMNAMEFIELDs in the TEMPFILE.

----TYPE: Text

----Set to Lookup. Field 1 looksup COMPANY::ITEMNAMEFIELD 01, field 2 looksup field 2 in company and so on.

-Create a field COMPANYNAME.

----TYPE: Text

----Global Field

-------You must create a relation between this field and the COMPANYNAME field in the COMPANY file.

-Create a field VALUELIST

----TYPE: Calculation

----Always evaluate

----Minimal Indexing

----Calculation:

If(Get ( RecordNumber )=1;ITEMNAMEFIELD 01;

If(Get ( RecordNumber )=2;ITEMNAMEFIELD 02;

If(Get ( RecordNumber )=3;ITEMNAMEFIELD 03;

If(Get ( RecordNumber )=4;ITEMNAMEFIELD 04;

If(Get ( RecordNumber )=5;ITEMNAMEFIELD 05;

If(Get ( RecordNumber )=6;ITEMNAMEFIELD 06;

If(Get ( RecordNumber )=7;ITEMNAMEFIELD 07;

If(Get ( RecordNumber )=8;ITEMNAMEFIELD 08;

If(Get ( RecordNumber )=9;ITEMNAMEFIELD 09;

If(Get ( RecordNumber )=10;ITEMNAMEFIELD 10;"ERROR"))))))))))

Now create 10 records.

Now create a script named "RELOOKUP" in the TEMPFILE.

-RELOOKUP FIELD [NO DIALOG;TEMPFILE::COMPANYNAME]

Then create a script named "UPDATE ITEMLIST" in the file you wish to create the valuelist in.

-SET FIELD [TEMPFILE::COMPANYNAME; valuelistfile::COMPANYNAME]

-PERFORM SCRIPT ["RELOOKUP" from file: "TEMPFILE"]

Create a valuelist pointing to TEMPFILE::VALUELIST

Now create a button next to the itemlistfield, which runs the script "UPDATE ITEMLIST".

When you run the script, it transfers the companyname to the temp file. Then runs the script "RELOOKUP" which lookup the list of items for that company into the temp file.

The Valuelist field in each record uses the data which was looked up.

Record 1 contains the item name from ITEMNAMEFIELD 01, record 2 from 02 and so on.

The valuelist now becomes populated with all the items, because it uses all 10 records from the temp file, each containing a specific item.

Hope that helps too.