Ever set up a diminishing or dwindling value list?
The same technique could be adapted here.
Use a calculation field that generates a return separated list of IDs that is never empty. (Include a dummy value such as a negative number that does not match to any possible record). Use it in a relationship with the ≠ to get a relationship that matches to all records that are NOT scheduled for maintenance. The dummy value is needed as a record can't be linked to records in a related table if the match field is empty.
Typical syntax for such a calculation field to be used in this fashion is something like:
-1 & ¶ & List ( ScheduledUnits::__pkIDField ) // select text as the result type
Thanks Phil, so this makes sense to me but what still confuses me is that the middle man (maintenance schedule) there may be times where we do not have any records associated to a particular day therefor we should see our entire fleet in the drop down menu. Starting from Dispatch based on the date chosen in Dispatch I need to then look at the calendar, if there are one or more units scheduled in that day for repair I need to then pull the remaining units which are available. Note that if there are multiple units in for repair there will be multiple records, one for each unit. I'm thinking this may cause an problem as well.
Last, what happens when there is no scheduled maintenance? Seems like filemaker would get confused as there is no records to evaluate in the middle man position.
Please note that I can give only general suggestions as I do not have a clear understanding of your data model from what you have posted to date, but:
there may be times where we do not have any records associated to a particular day therefor we should see our entire fleet in the drop down menu.
When there are no related records, the method I have suggested should link to all records in the database due to that -1 included to make sure that the field is never empty. (Make sure to clear the "do not evaluate if all referenced fields are empty" check box.)
My appologies, I'm not very good at breifing situations properly, also forgive me, I failed to mention a fourth table. So I have the following databases: Terminal, Calendar and Asset Tracker. The tables are as follows (Note I'll give the database name first followed by a dash and then the table name):
Terminal - DispatchCentral: This is where the date is selected which will ultimately determine which units I see in the drop down. The field is called "Date"
Terminal - RunDetails: This table contains all the particulars of a certain job run, like unit number, driver etc etc. This is where the drop down menu will be used, in particular, a field called "UnitID".
Calendar - Event: These records house the maintenance information like which unit is booked in for maintenance and on which day. The fields in question are "StartDate" and "UnitID"
Asset Tracker - Assets: This table houses our entire fleet, the unit number in question is stored in a a field called "InternalAssetID".
Note that all three data bases use the two file approach where the data is stored in one file and the layouts in another, I don't think this matters here but I thought I would mention it. Do you require any additional information Phil?
So what I see here represents 6 different files? As long as your external data source references and relationships are set up correctly, the multiple files involved should not be an issue.
However, my suggestions really can't go any further here and have to stay very general in nature as I have no idea how you've structured and related each of these tables.
Right, I didn't think the multiple files mattered either. Currently I have five files as I have not yet pulled out the calendar data into it's own file. I will most likely do that shortly but it's not urgent.
As for structure otherwise, I've tried to keep occurrences as isolated as possible so instead of having relational lines crossing all over the place and having copious amounts of occurrences to chose from in my "related tables" list I've instead created islands where the main occurrence sits in the middle and whatever needs a relationship added gets added around the one occurrence. Would that help you in anyway? I'm not sure I'll be able to pull this one off without your help. Something just isn't clicking for me.
It's the details that matter what records match between what tables by what match fields. What you describe as "islands", others describe as Auto Fill. That's a very good design method, but doesn't help me to help you.
Anything else I can provide you with to help me achieve my goal?
You really haven't provided the details that I need to comment further. To repeate from my previous post:
It's the details that matter what records match between what tables by what match fields.
I need to be able to see and understand your current design before I can say more. One way to do that is to upload a screen shot of your relationships graph with additional info describing how each works so that I can match up table occurrence boxes to layouts.
If you refer to the image I've uploaded you will see that I have the anchor buoy style you've mentioned earlier. My naming convention is as follows:
Notice the three occurrences in the top left hand side. Those are the occurrences I'm assuming I will need to achieve my goal but I'm ultimately unsure. All my attempts have not yielded the appropriate results. I think for ease and clarity, I will copy paste some information I had mentioned earlier about my tables:
Does this suffice or do you require additional information?
Hello Phil, have yo had a chance to review the image?
I've been on vacation since Wednesday. Having most of a week to forget all this stuff makes it that much harder.
What I would like to have is a drop down that only shows available units for one particular day within DispatchCentral based on what's been booked in Events.
Am I correct that you have a single record in DispatchCentral for a given date? And that your value list needs to list all IDs from Assets that do not have a corresponding record in Events for that same day?
Yes exactly! So I can have any number of units booked in for repair in my events and when those units are booked in for repair I want to remove the ability to see them in the drop down. The date selected on the record in DisdpatchCentral would then call upon the matching dates in events and remove the correcponding units booked in.
That means that if you put a portal to events on your DispatchCentral layout, you want a value list that lists all assets EXCEPT those that appear in the events portal. This is exactly what the method that I am suggesting should be able to handle.
In your DispatchCentral table, define this calculation field:
List ( -1 ; List ( Events::InternalAssetID ) )
And clear the "do not evalaute if all reference fields are empty" check box. The -1 serves as a way to make sure that this field is never empty by putting in a value for an asset ID that does not exist and will never exist in your table. We'll call that field, cScheduledAssetIDs
But your screen shot does not show any table occurrences for Events or Assets as far as I can tell--so I am creating in vapor here at the moment.
You can then define this relationship between DispatchCentral and Assets:
DispatchCentral::cScheduledAssetIDs ≠ Assets|Unscheduled::InternalAssetID
Your value list would then be set up to list values from Assets|Unscheduled, include only related values starting from DispatchCentral. (I'm not trying to reproduce your naming conventions to save time flipping back and forth between your screen shot and here. You'll have to subsitute your names for mine.)
And you've indicated that you need to use this value list for data entry into a different portal that Dispatch Central. That should work as long as you have a valid relationship to the correct occurrence of DIspatch Central (the same as that used in the value list and the relationships shown here) that matches to only one record in Dispatch Central.