new developer question

[brad2101]

Gday all,

 

I'm new to FileMaker and seem to be a bit confused.

 

I have four tables, 'job', 'bay', 'allocations' and 'customer'.

 

What i am trying to do is display a drop down that allows a user to see a list of the currently 'EMPTY' bays, then select that to set the bay to 'FULL' in both the allocation and bay table.

 

The bays also have another field that i want to choose not to display from, 'baycode' ie 'Y3'.

 

I've managed to display the fields only on the 'EMPTY' condition via a relation ship and a calculated field.

 

The problem is, i cant seem to get the second field to limit the display. Using another calculated field. Any advice would be greatly appreciated.

 

All i'm really after is something that gives me the results of:

SELECT * FROM bay WHERE status='EMPTY' AND bayCode LIKE 'Y%'

 

Then id want to insert the new records into the allocations and update the given bay record for the returned result, i imagine via a script.

 

But again any advice would be appreciated.

[CobaltSky]

Gday all,

 

I'm new to FileMaker and seem to be a bit confused.

 

Hi Brad,

Not least amongst the things you may be confused about is the fact that you seem to want to store the status of the bays in two separate places (in different tables). This suggests that your data structure needs a re-think. A fundamental principle of good relational design is that each piece of information should be stored only once.

 

If you are storing the status of the bay in the allocations table, you should not also need to store it in the bay table (you should be able to refer to it via a relationship between the two tables). Conversely, if you store it in the bay table, you should be able to access it from the allocations table. There should be no necessity to store it twice (and every reason not to).

 

Notwithstanding that, your actual question was about how to present users with a list of currently empty bays. There are several ways to achieve the effect you have described. One would be to:

 

1. Create a calculated number field called cEmptyBay_key in the bay table, defined with the formula IsEmpty(bay::status).

 

2. Create a global calculation field called cFilter_key in the current table (the one where you want to display the drop-down list) and enter the formula as 1.

 

3. Create a new TO of the "bays" table and call it EmptyBays.

 

4. Create a relationship between the current TO and the EmptyBays TO that matches cFilter_key to cEmptyBay_key (an equi-join).

 

5. Define a value list to return values from the EmptyBays::baycode field, plus values from the EmptyBays::bayname field (whatever it's called), then select the option to "Include only related values starting from" and select the current TO (the one that the layout you want to access the list of empty bays from is based on). Also select the option to "Show values only from second field".

 

6. Create a global text field in the current table called gSelectedBay.

 

7. In the Field/Control setup, define the gSelectedBay field as a pop-up menu (in the control style settings at the upper left of the dialog).

 

8. Create a script that changes the status to FULL for the bay selected in the gSelectedBay field and clears the gSelectedBay field.

 

9. Create a button to run the script (see 8 above), label it "Mark bay as FULL" (or something similar) and place it next to the gSelectedBay field on the current layout.

 

With the above in place, you will be able to select a bay from a list of empty bays, then click the button next to the selection field to change the status of the selected bay.

 

I have not gone into details about how the script at step 8 might be structured to change the status of the selected bay - but that's not the question you asked (and, in fact, is a whole other story, depending on exactly how you've set up the relational structure of your solution). smiley-wink

[brad2101]

Thank you very much CobaltSky,

 

My data structure wasn't the best as you had mentioned, and changes have been made as one to many relations from bays to allocations and one to many for jobs to allocations.

 

Your comments have been very useful, i have followed them but receive an error '' when i click on the gSelectedBay Field. I believe this would be because of something to do with my relationships. I assume that the that gSelectedBay as supposed to be used the values to display the value list defined.

 

I don't particularly understand the use of the 'cFilter_key' as a value of 1, perhaps as a constant?so i am a little lost as to where to go now.

[CobaltSky]

...Your comments have been very useful, i have followed them but receive an error '' when i click on the gSelectedBay Field. I believe this would be because of something to do with my relationships.

Brad, if you were to say what the error you receive is, it might be possible to suggest a way to address it.

 

I don't particularly understand the use of the 'cFilter_key' as a value of 1, perhaps as a constant?so i am a little lost as to where to go now.

Yes, the cFilter_key value is a constant of 1.

 

When matched via relationship to EmptyBays::cEmptyBay_key, it will enable the value list to return values from the bay records that have a status of EMPTY.

[brad2101]

Sorry, the error i am receiving is:

no values defined

 

Which i guess isn't really an error, just where the table relations are not correct.

[brad2101]

Thanks for your input.

 

I've looked alot around and i guess what i was trying to is more like

http://filemakertoday.com/com/showthread.php?t=17206

issue.

 

I now have the information i wanted displayed using the above post method.

Now i guess i just need to write a script to update the value of the bay status.

 

Again thanks for your help

[CobaltSky]

No problem. Glad to hear you're on track. So Cool!