Trouble using new layout/table for really custom "dialog box"

[afusaro]

I am designing a script that will allow me to query the user what type of object they want to store and based on that, give them a choice of conditional locations/storage units in which that type of object can be placed. Then, depending on their selection of storage unit, the script sorts another table for available spots in the selected storage unit and assigned the object to the first available spot.

Using a suggestion in the FM Pro 9 Missing Manual (p. 571), I am querying the user using a window, a layout, and a script. The problem is that when the user enters their choice of object type and storage unit, the first record in the table from which the "dialog" layout is created becomes modified to the user's choice and then not all choices may be available for subsequent queries.

Is there a way that I can query the user for their storage unit choice without modifying a table in which the value was originally stored? I think I might need to use a GoTo related record somewhere in there, but I don't think that will solve all my problems, since there are multiple storage unit options for each object type.

My two tables are (SampleStorageLocations) and (Storage). SampleStorageLocations has two fields: SampleTreatment (the type of object) and StorageLocation (the storage units that hold particular object types).

The window/layout that appears in the script gives a choice of SampleTreatment and then for StorageLocation, I have a drop down value list for that only shows related values of StorageLocations for the given SampleTreatment. The selected storage unit is then used to set a variable for assignment in a record of the Storage table later in the script.

A subset of my script with the relevant steps is below:

New Window []

Go to Layout ["Storage Location Selection" (Storage)]

View As [View as Form]

Show/Hide Status Area [Lock; Hide]

Adjust Window [Resize to Fit]

Allow User Abort [Off]

Pause/Resume Script [indefinitely]

Close Window [Current Window]

Set Variable [$StorageUnit; Value:SampleStorageLocations::StorageLocation]

...

Hope I haven't made that description too complicated. Thanks for your help!

[bcooney]

Sounds as if you need a dwindling value list. Watch Matt Petrowsky's video. Link

[afusaro]

I'm not sure that I want a dwindling value list, as I don't want the possible sub-options to disappear after their use. I want only a subset of options available depending on a certain condition. I think my bigger problem may be not permanently storing the second value with the first value. Am I missing something with the dwindling list function? Thank you!

[Fenton]

What you want is called a "conditional value list" or a "relational value list". The 2nd phrase describes the mechanism used, a value list based on a relationship.

Alternatively you could use a portal based on the same; which would involve showing the result somewhere; could be on the layout, could be on another layout, could be the same window, could be a new "pop-up window" (kind of like your script). Which you use partly depends on how many (value lists with thousands of values are kind of slow) and personal preference for your situation.

First thing, the fields in which the user is entering data in order to show the above should NOT be the "reference" table itself (in your case the user entry table is SampleStorageLocations and the reference table is Storage). If you have fields from Storage on the layout, it will not work, and you'll be modifying the reference data (which it sounds like you're doing).

My two tables are (SampleStorageLocations) and (Storage). SampleStorageLocations has two fields: SampleTreatment (the type of object) and StorageLocation (the storage units that hold particular object types).

Both these fields must also be in Storage. Maybe they are, but you didn't say about Storage.

The relationship would be:

SampleStorageLocations::SampleTreatment =:Storage::SampleTreatment

The value list would use the above relationship, showing StorageLocation as the field. You would also specify:

Starting from: SampleStorageLocations.

The SampleStorageLocations::StorageLocation would be the field on the SampleStorageLocations' layout, using the value list as a drop-down or pop-up.

The first field entered would be SampleStorageLocations::SampleTreatment. It would have its own value list, which would simply show all values from Storage::SampleTreatment.

[afusaro]

Thanks for the tips, Fenton. I'm already using a relational value list for the available storage unit options, but I don't think I've completely solved how to set up the table/layout relationships for the pop-up window in my script. Here are a few clarifications:

1) My user entry layout is actually coming from the Storage table, while SampleStorageLocations is my reference table. The SampleStorageLocations table consists of eight records (one for each storage unit) and includes only two types of sample treatments.

2) Actually, I don't want the records of either table to be modified when the user pop-up window/layout is edited (i.e., the user selects a treatment and then a storage unit from the relational value list defining which are available for that treatment). Do I have to have a third table in which this information is edited and use that for the user entry window? I only want the unit options to be limited by the treatment type and then the user to select a unit which will be set to the variable $StorageUnit. I don't want any record to be changed at the time. Is there a better way to query the user (using relational value lists, if possible) and then use their selection to set a variable for later in the script? Unfortunately, each of the two treatments has four conditional storage unit options, so I have one too many options for a simple Custom Dialog interface.

Thanks again!

[Fenton]

You say you don't want either table to be modified. This implies a 3rd table, with the 2 existing tables being "reference" tables (data entered by you or someone, but not part of this particular data entry).

So, you could either put the fields also in that table, with value lists, the 2nd list relationally filtered, then choose them. Or you could the 1st of them in as a global field, then choose them. It doesn't matter much to FileMaker whether the originating side of the relationship is a regular or global field, in this case.

It would be best if you built a simple example file, with some reference data, and with what you are thinking is the data entry table you're thinking of. Because this is (way) simpler to show than to explain, for either of us.

[bcooney]

I am designing a script that will allow me to query the user what type of object they want to store and based on that, give them a choice of conditional locations/storage units in which that type of object can be placed. Then, depending on their selection of storage unit, the script sorts another table for available spots in the selected storage unit and assigned the object to the first available spot.

It was my understanding that the user is on an Object record, and that they are ultimately assigning a Storage Unit and Storage Spot for that Object. So, an Object record would have an ObjectTypeID, StorageUnitID and StorageSpotID. As the user specifies ObjectTypeID, you'd use a relational value list to narrow the value list of Units to those of the specified type. It was at the StorageSpotID field that I realized that I only want available Spots (related to the selected Unit) in this value list. I need a flag_Available in Spots. Unfortunately a Spot can only tell if it's used by looking up to the Object table and seeing if its ID is assigned to an Object. That means that it's an unstored flag, and as such cannot be used to filter a value list.

Matt uses a script trigger, I assume, to set a field in Spots that would allow for a stored "available" in Spots. Then you can filter from Objects>StorUnits>Spots, only showing available.

[afusaro]

Here is a downsized file that demonstrates the action of the Assign Extract Storage Location script from the button on the ExtractTable layout and includes the related tables/layouts. I've given the Guest Account login full access (no password).

For now, I've created a temporary fix to relabel the referenced StorageLocation in the SampleStorageLocation table (layout hidden), which I'm using as the user input window. This fix is detailed in the middle section of my script. It would be better if I didn't have to include it, so if you have any suggestions for addition tables once you see the file, please let me know. (The original script, without this "fix," can be accessed from the Scripts menu.)

When you run the script, select "Frozen" and "03 (-80C Pasteur) for the options in the pop-up window, as I don't have the other storage units included in this file copy.

Test_fusaro.fp7.zip