Getting a value list from a field

[Aussie John]

Hi

I have a related field I wish to use as a value list. I know how to do that, but I need to filter out the values that arent part of the relationship. Trouble is when i select "include only related values" the pop up doesnt work any more. if I select all values then I get too much and not related info in the list.

thanks for any ideas

I'm using v7

Version: v6.x

Platform: Mac OS X Panther

[Vaughan]

The usual way is to have the values in a related database (or table in FMP 7). It could be a self-join too where appropriate.

Say you want to filter a list of cities based on the selected state. You'll need a database (table) where each record has a field for sity and another for its state.

In the main database you'd have a relationship going to the cities table based on the state field. Now define the value list to only display cities based on the states relationship.

[Aussie John]

Thanks Vaughan, slowly getting the hang of this darn thing.

Just one quirk though

The item in the value list is the first entry point for the portal (and a new record) but the value list only works if a record has already been created, In other words if I type anything first, then the pop up list works. I have all the allow creation new records selected in the Edit Relationship dialog.

Version: v7.x

Platform: Mac OS X Panther

[Vaughan]

Hmmm, I never thought of that... there can be no relationship yet because the key field has no entry yet. On the other hand, if it is a related or conditional value list the you need to first enter the data that the value list is related to.

I'd look for an "interface" solution: design the interface so that the user is required or encouraged to enter the data that the value list relates to.

PM me your contact details.

[Ugo DI LUCA]

My 2 cents. Try an exit record step prior to jumping into this portal for selection, just after the record gets created.

Version: v6.x

Platform: Mac OS 9

[Aussie John]

Thanks Ugo but I'm not entering the value list via a script. Regardless Itmakes sense the value script can only work once a new record has been created otherwise there is no link. I might have to make a script to create the record first.

[Vaughan]

Not so much that the record hasn't been created, but that the key field that the relationship is based upon has not yet been entered.

A "normal" value list will display OK in the blank portal row even though the record has not yet been created.

[Ugo DI LUCA]

Hi John,

The script I was referring to was the "Create New Record" in the Main file, if this New Record is the Left key of your relationship.

As it seems, from your first post, that your value list also is related to this new record ID, the value list wouldn't be active until this new record has been commited.

This Exit Record script step, which would need to follow a New Record/Request step, would force commitment of this new record.

If instead, you simply create your record and then immediately jump (with or without a script) to the portal, then your record wouldn't be commited.

This also explains why, in some situations, lookups would fail in the related file. If part of your fields in the related file are filled through lookups from a relationship to the Main file, these would fail if you didnt exit the record right after it has been created. and jumped immediately into the portal fields (with or without a script).

I hope this is help.

FileMaker Version: 6

Platform: Mac OS 9