1 2 Previous Next 15 Replies Latest reply on Oct 4, 2012 12:33 PM by copesetic

    Relational Fields

    copesetic

      Title

      Relational Fields

      Post

           I am having a problem with a foreign key field.  I have a table that will have 3 separate customers on it.  I am trying to pull the data from my customer table using the the foreign key field on my main table.  The problem that I am having is I can't get the 3 different fields to use the customer table individually, when I change one they all change to the same selection.  I feel like I should be able to have different fields on my table reference the customer table with different entries but I can't seem to get it to work.  Anyone have any solutions?  Thanks in advance.

        • 1. Re: Relational Fields
          philmodjunk
               

                    when I change one they all change to the same selection.

               That suggests that you only have one field, but three copies of the same field on your layout. If you enter layout mode and select each field in turn, do you see the same text in "display data from" in the Inspector's Data tab? If so, you have three copies of the same field. The first thing you would need to do is define three separate fields in Manage | Database | fields.

               Then you might need three separate relationships to different occurrences of the same customer table, but that depends on what you intend to do on this layout. And it may be that you need a portal to three related records instead of three different customer fields.

          • 2. Re: Relational Fields
            copesetic

                 Yes I am using the same "data", so I guess that is where my confusion comes from, I don't know how to select 3 distinct customers from that table, I haven't used a portal before.  Do you think that is the best way to do it?

            • 3. Re: Relational Fields
              philmodjunk

                   I lack sufficient detail about what you want to do to suggest an answer.

              • 4. Re: Relational Fields
                copesetic

                     To clarify, are you telling me that if I have a "customer" table with a primary key of "custID", that I won't be able to pull more than one of those at a time into another table without using a portal?

                • 5. Re: Relational Fields
                  philmodjunk

                       No I am not. If you read my first post, you'll find that I said: "The first thing you would need to do is define three separate fields in Manage | Database | fields. Then you might need three separate relationships to different occurrences of the same customer table,"

                       It is possible that your design will be simpler and more efficient using a portal.

                  • 6. Re: Relational Fields
                    copesetic

                         I saw that I guess my brain didn't compute what you were asking.  I guess I don't totally understand what those fields would be.  Right now I have CustomerName, CustomerEmail, phone, street, etc. etc. with CustomerId being the primary key.  Can you give me some insight as to what the 3 separate fields would entail?  Sorry for being such a rookie...

                    • 7. Re: Relational Fields
                      philmodjunk

                           I wouldn't want to help you complete a design that might not be the correct way to go with your database.

                           I'd prefer a description of your layout and how you are trying to use it. In particular, I'd like to know why you need fields for 3 customer ID's for the same record as that is a bit unusual. That way I can recommend an approach best suited to your needs.

                      • 8. Re: Relational Fields
                        copesetic

                             haha, I realize the 3 customer ID's is unusual, ultimately this data is on real estate transactions and so each record has a listing agent, a selling agent and the escrow officer, and we need to have all 3 tagged to this particular property that is being sold.  Is that more helpful?

                        • 9. Re: Relational Fields
                          philmodjunk

                               Very much so. Is the number of parties alway limited to no more than 3? If there is even the slightest chance that you need more than three in some circumstances, you need a portal to a related table so that the number of "customers" listed for a given record can be fully flexible.

                               Here's what I understand so far:

                               You need  field for both agents and the escrow officer--all from the same table named "customer".

                               Use Manage | Database | Fields to define three number fields: SellingAgentID, ListingAgentID, EscrowOffID. I'll call the table you have for these fields RealEstate, but you can stick with the name you have.

                               Now we need 3 different "occurrences" of your Customers table...

                               In Manage | Database | relationships, make a new table occurrence of Customers by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as ListingAgent.

                               We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                               Add it to your relationships like this:

                               RealEstate::ListingAgentID = ListingAgent::CustomerID

                               Repeat the process to get:

                               RealEstate::EscrowOffID = EscrowOfficer::CustomerID

                               And finally:

                               RealEstate::SellingAgentID = SellingAgent::CustomerID

                               You now have three different relationships between RealEstate and different occurrences of your Customers table.

                               You now have two options for showing the CustomerName, CustomerEmail, phone, street, etc. etc for each of these. You can, for example, add the CustomerName field from ListingAgent to your layout and it will display the ListingAgent's name when you select an ID number in ListingAgentID. Or you can define a text field in RealEstate and set it to auto-enter the CustomerName from ListingAgent via a "looked up value" setting in field options. The first approach means that any changes to data in the customers table automatically update when you examine your records in RealEstate. The second option captures the data that was current at the time you selected a CustomerID number in the field. This then will not automatically update if you edit the customer name in the original table. Both options are useful, you need to determine which is best for your specific database.

                          • 10. Re: Relational Fields
                            copesetic

                                 Fantastic, I will give this a shot!

                            • 11. Re: Relational Fields
                              copesetic

                                   And I assume when you say "Add it to your relationships" that is done on the relationships graph and I am just dragging the fields to connect them?

                              • 12. Re: Relational Fields
                                copesetic

                                     I assume when you say "Add it to your relationships" you mean in the relationship graph, and I am just dragging to connect the fields?

                                      

                                     sorry if you already got this when I posted this question a second ago I am not sure it went through

                                • 13. Re: Relational Fields
                                  copesetic

                                       When I am in Browse mode and try to use those fields I get this message.

                                  • 14. Re: Relational Fields
                                    philmodjunk

                                         Yes you need to drag from a field in one table occurrence "box" to a field in another to link them in a relationship. You can then double click the line to open up a dialog where you can specify more details about a given relationship if needed. (Not something you should need to do here.)

                                    1 2 Previous Next