8 Replies Latest reply on Dec 27, 2010 3:52 PM by SlevinKelevra

    relationships with more tables issue



      relationships with more tables issue


      Hi gentlemen, I'm new in this forum, thanks for your help.

      My problem is:

      i have one table: codetable--->code1,code2,description

      and one table: itemused--->quantity,codeused

      the relationship is codetable::code1=itemused::codeused so if i put in the field itemused::codeused one of a code stored in code1 i can catch the description of the item. But the item could have 2 codes (code2). How can I proceed in order to have the possibility to put in codeused one of the code2 and catch the same description?

      Sorry for my "little" english, and marry chritmass!



        • 1. Re: relationships with more tables issue

          None of your table occurrence (boxes in graph) names in your relationship screenshot match the names you used in the text you posted. None are named codetable nor are any named ItemUsed.

          Going just from the text you've posted, you can create a second table occurrence of ItemUsed and link to it by CodeTable::Code2 instead of the code1 field. To create a new table occurrence, find ItemUsed in Manage | Database | Relationships, select ItemUsed by clicking it, then click the button on the bottom left corner with two green plus signs.

          If your added relationship looks like this:

          CodeTable::Code2 = ItemUsed 2::CodeUsed

          Then you can place ItemUsed 2::DescriptionField on your layout to display the matching description.

          If Table Occurrence is an unfamiliar term, you may want to read this thread:  

          Tutorial: What are Table Occurrences?

          • 2. Re: relationships with more tables issue

            thanks for your attention, and sorry for the confusion. I try to explain with my real field. 

            I had change the screenshot.

            As you can see I have spare used::su_codice DNT=item_supplier::supplier_code and this code is linked to my item table with a key id, so in my layout if i put in su_codice DNT one of the supplier code I can visualize the description (I have in the layout the field item::description).

            How can i visualize item::description if spare::su_codice DNT=item::dnt_code also?

            thx, Fabrizio.

            • 3. Re: relationships with more tables issue

              You have that set up in your relationship graph already.

              Simply add the description field from item 2 instead of the description field from item.

              • 4. Re: relationships with more tables issue

                yes... but after I can only see the description if my code is one of the dnt_code.  ?

                • 5. Re: relationships with more tables issue

                  Well you want to show the description via one of two different relationships. That requires that you either have two such fields, one from each table occurrence on your layout or that you construct a calculation that combines the data from the two sources into a single field.

                  How you do such a calculation depends on the results you want. In theory, either Item 2::description or Item::description could be empty (or refer to a record that doesn't exist.).  They could also refer to different records with completely different item descriptions. What do you want to see on your screen in each of these cases?

                  Why do you have what appears to be the same data in two different fields of spares used? (What problem does that solve for you?)

                  • 6. Re: relationships with more tables issue

                    Maybe it's incorrect my table structure. I have the table fault_maintenance that is a list of report where there are a list of items that I have used in every report.  This items has a supplier code or more supplier code, (item_supplier::supplier_code) So if i use one of the supplier code for the itemA i can see the correct itemA description... but, I have also an internal company code for the same itemA, but in a different field. When I compile the report and i put the item used for the maintenance, I would like that doesn't matter what kind of code I will put on it, company code (DNT_code) or the supplier code... it will give me the description of the item. 

                    What you think? It will be possible? 

                    • 7. Re: relationships with more tables issue

                      It's possible, but somehow the system has to tell the difference between supplier codes and and dnt codes. Putting both codes in the same field would seem to be a recipe for confusion.

                      I'd set this up so that either entering a DNT code looks up the supplier code in a separate field or that entering a supplier code looks up the DNT code in a separate field. That way, you always have both codes whenever you enter one of the two codes and then you can have a single link to Items that will work in both cases.

                      If you insist on the current structure, you'll need to make sure that any DNT code can not ever match a supplier code. Then a calculation such as:

                      If ( IsEmpty ( Item::Description ) ; Item 2::Description ; Item::Description ) can display the description no matter which code is entered in su_codice DNT.

                      • 8. Re: relationships with more tables issue

                        thank you for your patience, you have solve my problem!Laughing