If you define this relationship:
audit::idfield = Lamps::idfeild lamps
Then audit::BGE Device Code can be given a "looked up value" auto-enter setting that copies a value from Lamps::BGE Code.
I actually thought the same, but I must be having some other issue. When I define the relationship of audit::idfield = Lamps::idfeild lamps - I loose all my other value lists that are in Audit that reference Lamps with related values only. There are no options any longer for any of the (3) fields I have setup like that
awesome, i created a Lamps 2 table and then did as follows
Ok - here is the next step i'm trying to accomplish -
I have a field Audit::utility code. That field is a value list with just a few selections that i've pre-entered.
I have a table Wattage table. In this table I have a field "device code" which i'll need to setup as a lookup field from audit:BGE Device Code.
On Wattage table i'll have fields that will show wattages per device code that will need to be looked up. I need to lookup the wattage based on the selection chosen in Audit::utility code.
I'm just not sure how to work all this out...thanks
to add to the above I have already created a relationship between Audit:BGE Device Code and Wattage table::Device Code.
just not sure of the next step
You have two options for showing the matching Wattage for the device code specified in audit. Both methods use the relationship you have defined so you are halfway there.
You'll need to decide whether it fits your needs better to copy (look up) the data from Wattage or to just link to that data. With the first option, the wattage value in any given audit record does not change when you edit a wattage value in the matching wattage record. The second option will update automatically.
To copy the data, set up the same looked up value field option on a Wattage field in audit just like we described earlier for another field. To link dynamically to the wattage, use the field tool to add the field fron Wattage directly to your Audit layout.
Linking worked perfectly...but so I can understand...how did that know to pull the correct wattage from that table?
I have already created a relationship between Audit:BGE Device Code and Wattage table::Device Code.
Thus, the values in your match fields match, do they not?
A couple more steps i'm trying to accomplish that I could use help on in regards to this. I'm thinking these will be calculations
First - In my Wattage Field i've currently setup it up twice to play around with using your suggestions above. One is a link and the other is a lookup. Both work fine at this point. However at times I'll have no data in Lamps::BGE Code which as in the above is being used to as a lookup value for Audit::BGE Device Code. That BGE Device Code is than being used to lookup or link to the Wattage Table:Wattage Field.
When that field Lamps::BGE Code is blank then I want to enter into Audit:Wattage Field a calculation of Audit::Number of lamps x Audit:Lamp Wattage
Can this be done?
Second - I mentioned in my post above from 7:38 am "I have a field Audit::utility code. That field is a value list with just a few selections that i've pre-entered." During the step of looking up the BGE Device Code I would like the field of Audit::utility code to point to the correct Wattage Table to use. The reason for this is different utility companies mandate the use of slightly different wattages for each lamp type / size / wattage etc. I'll either need multiple tables (1) for each utility company OR what I prefer to do is just have multiple fields in my Wattage Table for each Utliity company. I'm just not sure how to setup the Audit::utlility code to trigger which field gets the lookup.
You have a problem, here: If Lamps::BGE Device Code is blank, there is no record in Lamps that is linked to a record in Audit.
I recommend that you do neither of those. Managing multiple tables for the same type of data is poor database design and in FileMaker, it will make for a very inflexible design. Multiple tables in your Wattage table will also be poor design and inflexible.
Instead, create separate sets of records in your wattage table for each utility. Then include a Utility Field in the relationship that matches records in Audit to Records in Lamps.
Yes I was thinking the linking option would work. Is there a way to use some sort of IF statement in the lookup to have it first look to see if the field is blank and if so make the calculation otherwise do the normal lookup?
correction in my statement above...I was thinking the linking option would NOT work
In regards to the utility wattages, I believe im beginning to understand. Is this how I should handle it -
In my wattage table I need to duplicate all the current records and then enter the correct wattages for the new utility in those records. I need to place a utility code in the wattage records to separate which records go with which utility.
I need to create a relationship between Audit:Utility code and a new field I create of Wattage Table::Utliity code
I'll need to create a new TO of Wattage Table
I'll need to...nope I guess I don't have it....
not sure how i'd link