1 2 Previous Next 16 Replies Latest reply on Sep 9, 2014 6:41 AM by BorisKamp

    Multiple relationship per table workaround

    BorisKamp

      Title

      Multiple relationship per table workaround

      Post

           Hi guys!

           I have the following situation: see screenshot

            

           The two tables 'AWF1' & 'AWF2' need to be related to the 'uitkeringen' table AND to the 'contactpersonen' table.

           How can I do this?

           'AWF1' & 'AWF2' contain fundinformation about a person ('contactpersonen') the table 'uitkeringen' is used as a portal and will be used to keep track of payments for both funds.

            

           I hope Im clear enough and somebody can help me out.

      Screenshot_2014-08-01_17.00.27.png

        • 1. Re: Multiple relationship per table workaround
          philmodjunk

               No work around is needed.

               Create a new table occurrence of uitkeringen so that you can link AWF1 to one occurrence and AWF2 to the other. For more about Table Occurrences, see: Tutorial: What are Table Occurrences?

          • 2. Re: Multiple relationship per table workaround
            BorisKamp

                 Thank you for your reply!

                 I was thinking of this but thought there might be a 'cleaner' way to solve this? Literally making a copy of the table is an option, but what if you get to dozens of tables? Is this the best way?

                  

                 thanks!!

            • 3. Re: Multiple relationship per table workaround
              philmodjunk

                   You are not copying any tables by doing this. You are simply duplicating the reference to the table. If you follow the steps for duplicating a table occurrence as spelled out in the tutorial for which I provided a link, you can hover the mouse over the arrow in the top left corner of each table occurrence box and see that both refer to the same table.

              • 4. Re: Multiple relationship per table workaround
                BorisKamp

                     Phil! I still have an issue.

                     first please take a look at the screenshot of my relationship schedule.

                     I created a TO of 'uitkeringen', and linked it to the 'awf2' table.

                     My goal is to be able to enter payments to each person in either fund AWF1 or AWF2 or both.

                     right now when I enter a payment in fund AWF1, it enters the same payment in fund AWF2, there's the problem. How can I solve this? they need to be independent from eachother but based on the same table, now is that possible? or do I need to create two completely different tables

                      

                     Thanks!

                • 5. Re: Multiple relationship per table workaround
                  philmodjunk

                       Am I correct to assume that recording a payment requires recording it as a new record in uitkeringen?

                       Both of your relationships match to the same contact ID value in the same field in uitkeringen.

                       You'll need to change one of these relationships in some way to keep them separate.

                       One method is to define two match fields in uitkeringen, such as one named: _fkContactIDAWF1 and another named .

                       Use the first match field to link to records in AWF1 and the second to match to records in AWF2. Then, when you record a payment linked to a record in AWF1, the _fkContactIDAWF2 will be empty and the opposite will be the case when recording payments linked to records in AWF2.

                  • 6. Re: Multiple relationship per table workaround
                    BorisKamp

                         Yes you are correct. but Im not sure about your last paragraph, you say 

                         

                    the _fkContactIDAWF2 will be empty and the opposite will be the case when recording payments linked to records in AWF2.

                    But I need to be able to fill in both.

                    I created this now, and get an error when I want to enter a payment in uitkeringen table (both ones)

                    I get this error : "This field cannot be modified until “_fkContactIDAWF1” is given a valid value. " or this one : "This field cannot be modified until “_fkContactIDAWF2” is given a valid value."

                         thanks for the help!

                    • 7. Re: Multiple relationship per table workaround
                      philmodjunk
                           

                      But I need to be able to fill in both.

                      But not in the one record for one payment. Payments linked to a record in one table should not also be linked to a record in the other table.

                           Exactly how is your layout set up to record these payments?

                           On what layout and based on which table occurrence box? Are there portals to the two different occurrences of uitkeringen on that layout?

                      Did you set a "not empty" validation option on these two fields? (If so, remove this option as it should not be specified.)

                            

                      • 8. Re: Multiple relationship per table workaround
                        BorisKamp

                             Hi Phil,

                             Sorry for my late reply, had no time to work on the db in the meantime, here's my answer to your question:

                             MY layout is setup with two tabs, AWFI & AWF II. Inside those tabs we have the option the enter the payments for the corresponding fund, so in AWFI tab, you can only enter AWFI payments etc.

                             The payments are processed trough a portal, the portal of AWFI is linked to "uitkeringen" and the portal of AWFII is linked to "uitkeringen 2", this is the TO of "uitkeringen".

                             I have no validations set.

                             If you need to know more, please let me know. Can I fix this with a TO, or do I need to make tables "uitkeringenAWFI' & "uitkeringenAWFII" instead of just "uitkeringen" and her TO's

                              

                             Thanks!

                        • 9. Re: Multiple relationship per table workaround
                          philmodjunk

                               One key question remains unanswered:

                               On what layout and based on which table occurrence box?

                               In other words. Your portals and tab control are placed on a layout. That layout specifies a table occurrence in Layout Setup | Show Records from. What Table occurrence is specified there?

                               So far, I see no reason for that error message that you have reported, but the table occurrence specified for your layout might explain that issue if you don't have a validation field option set to require a value in both of these fields in the same record.

                          • 10. Re: Multiple relationship per table workaround
                            BorisKamp

                                 Ah Ok, I think I get it,

                                 but I think I mentioned that in my last post right? when I enter portal setup it says: "Show related records from: Uitkeringen" in the AWFI tab. It shows "Show related records from: Uitkeringen 2" in the AWFII tab.

                            could it have to do anything with my field names or something.

                            I will send you the file with a personal message

                                  

                            Thanks!

                            • 11. Re: Multiple relationship per table workaround
                              philmodjunk

                                   You have indicated the table occurrence for the PORTAL. I am asking for the table occurrence specified for the LAYOUT. It's the relationship(s) linking the layout's table occurrence to the portal's table occurrence that is critical to how the portal functions. So I need to know what is specified for both portal and layout in order to understand why it is not working for you.

                              • 12. Re: Multiple relationship per table workaround
                                BorisKamp

                                Hi Phil,

                                Did you get my personal message with the link to the file? I haven't gotten a response so Im wondering

                                Thanks!

                                • 13. Re: Multiple relationship per table workaround
                                  philmodjunk

                                  I've been waiting for you to answer my question. Working from the answer to the question I asked will be much faster than downloading and examining a file--especially since I can't read your language. (Sorry, but I help out here as an unpaid volunteer and there are limits to the time I have available to do so.)

                                  • 14. Re: Multiple relationship per table workaround
                                    BorisKamp

                                    I'm sorry, I understand!

                                    does this image help you out? I'm not sure how to read your question, sorry for that, so let me know if you need additional information.

                                    1 2 Previous Next