1 2 Previous Next 22 Replies Latest reply on Mar 1, 2013 9:28 AM by philmodjunk

    Lookup value?  Relationship help needed

    vincel

      Title

      Lookup value?  Relationship help needed

      Post

           In my database I have three tables (that matter for the help needed) named "Lamps", "Audit" and "BGE Wattage Table"

           In "Lamps" I have a field named "BGE Code" and one named "idfield lamps"  (amongst others)

           In "Audit" I have a field named "BGE Device Code" and one named "idfield" (amongst others)

           In "BGE Wattage Table" I have a field named "Device Code" (amongst others)

           My "Audit" table is my main table where i'm doing data entry.  As fields are filled the "idfield" is populated based on a & statement to join multiple fields from "Audit"  

           In my "Lamps" table I have all of the possible combinations that could occur from the "idfield" listed in the "idfield lamps" column.   

           What i'm trying to do is setup the field named "BGE Device Code" in "Audit" so that it will look at the "idfield" from "Audit" and then find that coorisponding value in "idfield lamps" in the "Lamps" table.   Then from there take the value from the "BGE Code" field from "Lamps" table and enter that value in that field (BGE Device Code) created in "Audit" 

            

           Hope that makes sense....I just can't figured this one out but I know it's probably easy!

        • 1. Re: Lookup value?  Relationship help needed
          philmodjunk

               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.

          • 2. Re: Lookup value?  Relationship help needed
            vincel

                 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 

                 Any ideas?

            • 3. Re: Lookup value?  Relationship help needed
              philmodjunk

                   Do these conditional value lists require a different relationship?

                   If so, you can have both relationships by adding an extra Tutorial: What are Table Occurrences? for one of your tables.

              • 4. Re: Lookup value?  Relationship help needed
                vincel

                     awesome, i created a Lamps 2 table and then did as follows

                      

                     audit::idfield = Lamps2::idfeild lamps

                      audit::BGE Device Code can be given a "looked up value" auto-enter setting that copies a value from Lamps2::BGE Code.

                     works perfect

                     thank you

                • 5. Re: Lookup value?  Relationship help needed
                  vincel

                       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

                  • 6. Re: Lookup value?  Relationship help needed
                    vincel

                         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 

                    • 7. Re: Lookup value?  Relationship help needed
                      philmodjunk

                           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.

                      • 8. Re: Lookup value?  Relationship help needed
                        vincel

                             Linking worked perfectly...but so I can understand...how did that know to pull the correct wattage from that table?

                        • 9. Re: Lookup value?  Relationship help needed
                          philmodjunk
                               

                                    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?

                          • 10. Re: Lookup value?  Relationship help needed
                            vincel

                                 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.

                                  

                            Thanks,

                            Vince

                                  

                            • 11. Re: Lookup value?  Relationship help needed
                              philmodjunk

                                   First:

                                   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.

                                   Second:

                                   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.

                                    

                              • 12. Re: Lookup value?  Relationship help needed
                                vincel

                                     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?

                                • 13. Re: Lookup value?  Relationship help needed
                                  vincel

                                       correction in my statement above...I was thinking the linking option would NOT work

                                  • 14. Re: Lookup value?  Relationship help needed
                                    vincel

                                         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

                                          

                                    1 2 Previous Next