The basic concept seems very easy to set up. But the details are lacking as two exactly what you mean by "depending on the selection I make in a field in that related table"
That field could be a key field that's part of the relationship or it could be a field referenced in a portal's filter expression (If using FileMaker 11) and then the selection made in this field would control what record in the related table provides the data shown in fields from that table on your layout.
Thanks Phil. This might reflect an incorrect structure but I couldn't think of another way to do it.
In my "cases" table, each record represents a new case (or new work). Each case has, if you like, a billing item I need to select (and which can be different for different cases) but then that billing item can be worth a different amount (or rate) in different cases. So I have a second table "items" that has the billing item number field and then several fields for the different amounts (eg "amount1", "amount2" etc).
After I have selected a particular item for the case from the items table, I want to be able to select "amount1" or "amount2" (etc) as the value for that item in that record.
Thanks in advance
Ok, what you describe is called a conditional value list. The Item you select can then control what values are available for selection.
Add a calculation field, cAmtList, to Items and define it as: List ( Amount1 ; Amount2 ; Amount3 ) //add as many fields as you have for the amounts.
Presumably you have this relationship between cases and items:
Cases::ItemID = Items::ItemID
If so, you can define your value list as listing values from Items::cAmtList, Include only related values, starting from Cases.
You could also set this up with an added table instead of dedicated amount fields and the same value list approach can be made to work. A portal ot this new table on your Items layout would be used to record the different amounts for each Billing Item record.
Hi Phil. Thank you for your time and effort. I really appreciate it.
I didn;t understand the last two lines but I have done everything up until then and have a field on cases that has a list that contains only the related amounts - but because only the numbers are listed, I don't know which amount is the one I want. Anyway, I don't want to select the amount manually, I want the amount to be selected (looked up ideally) for use in a calculation; so apart from selecting a record from "items" (which as you say makes a conditional list of the cAmtList values) I want to make another selection in another field in cases (which would have a value list of the names of the fields containing the amounts - "amount1" "amount2") that pulls up the appropriate amount and makes it available for a calculation or display
The last two lines describes a much more flexible option as you can add as many "amounts" as you want and can "name" them any away that you want--a flexibility that you may or may not need. The need to refer to the amounts by a name rather than the actual amount pretty much limits us to this "additonal table" approach even if you don't need that flexibility.
Define a table with at least these three fields:
ItemID; Amount; AmountName
Add this relationship:
Items::ItemID = ItemAmounts::ItemID
Make a new occurrence of ItemAmounts by selecting it and clicking the duplicate button (button with two green plus signs). Rename this occurrence and link it to Cases like this:
Cases::ItemID = SelectedItemAmount::ItemID AND
Cases::AmountName = SelectedItemAmount::AmountName.
Cases::AmountName is the field you will set up with the conditional value list of names.
Keep the original relationship I described in my previous post.
Set up your conditional value list to list AmountName from ItemAmounts, Include only related values starting from Cases.
You can either add the related Amount field from SelectedItemAmount to your cases layout or you can define a number field in Cases that uses a looked up value setting to copy this value from SelectedItemAmount.
If you add the field from the related table, any changes made to amounts in ItemAmounts will automatically appear in all records in Cases. If you use the looked up value auto-enter option, any changes of this type will only appear in newly created records in Cases unless you specifically udpate them and you can edit the amount in Cases without changing the amount shown in other Cases records that specify the same combination of ItemID and AmountName.
Hi Phil. Thanks again for your patience and time. Let me know when this gets tedious for you and I'll stop bugging you!
I set it up exactly as per your last post, then realised I needed it the otherway around - i needed to be able to select the amountname first, then select the item. So i have 3 tables, cases (ID, casedate, scheduleID, mainitemID), schedule - which we were calling amountname - (ID, schedule, unitrate), and mainitems (ID, scheduleID, itemnumber, itemunitvalue). I have these links: cases::scheduleID = schedule::ID, schedule::ID = mainitems::scheduleID, cases::scheduleID = mainitems2::scheduleID, cases::mainitemID = mainitems2::ID. I have a value list for cases::mainitemID based on table mainitem and including only related values starting from case. This list shows only the mainitemIDs that have the same scheduleID as the scheduleID selected in cases::scheduleID.
So, everything as you suggested but slightly the other way around. By the way there are 64 mainitemIDs X 12scheduleIDs = 768 records in the mainitems table. So at the moment my dropdown list for case::mainitems contains 64 items. I want to filter that list further by adding an "itemgroup" to the mainitems table (there would be 2 - 10 mainitems in each group) then selecting that itemgroup in the case table layout and have only those items that match the scheduleID AND the itemgroup appear, thus making this selection a more manageable 2 - 10 items.
Phil thanks again for your time - I spent many hours on this trying to work it out for myself, tried adding a third occurrence of the mainitems table etc - but I can't seem to "double filter" that mainitems value list.
Thanks again, Chris R
Sorry I should clarify ... there are 12 schedule IDs applying to 64 different itemnumbers (mainitems table) making 768 records in that table. Obviously there are 768 different mainitems::ID's
AS is frequently the case, I am hampered by not knowing everything you do about your database and how you intend to use it. The purpose of each of the three tables is a bit fuzzy at the moment. What I can parse out of all that is:
"I want to filter that list further by adding an "itemgroup" to the mainitems table"
This, again, is a conditional value list and will require an relationship that supports it.
Have you read these threads and looked at this demo file before?
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
Thanks for your help Phil. The concept of using portals to understand relationships, and value lists based on those relationships, has been eye-opening.
I have hit another snag now in the progression of my database related to a many-to-many relationship, but I shall start another thread since this one has answered my earlier questions. Thanks again