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

Multiple relationship per table workaround

BorisKamp Aug 1, 2014 8:05 AM

Title

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.

1. Re: Multiple relationship per table workaround

philmodjunk Aug 1, 2014 8:08 AM (in response to BorisKamp)

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?
Like Show 0 Likes(0)

Actions
2. Re: Multiple relationship per table workaround

BorisKamp Aug 1, 2014 8:21 AM (in response to 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!!
Like Show 0 Likes(0)

Actions
3. Re: Multiple relationship per table workaround

philmodjunk Aug 1, 2014 8:26 AM (in response to BorisKamp)

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.
Like Show 0 Likes(0)

Actions
4. Re: Multiple relationship per table workaround

BorisKamp Aug 1, 2014 10:21 AM (in response to 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!
Like Show 0 Likes(0)

Actions
5. Re: Multiple relationship per table workaround

philmodjunk Aug 1, 2014 10:31 AM (in response to BorisKamp)

     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.
Like Show 0 Likes(0)

Actions
6. Re: Multiple relationship per table workaround

BorisKamp Aug 1, 2014 10:48 AM (in response to 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!
Like Show 0 Likes(0)

Actions
7. Re: Multiple relationship per table workaround

philmodjunk Aug 1, 2014 11:19 AM (in response to BorisKamp)


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.)
Like Show 0 Likes(0)

Actions
8. Re: Multiple relationship per table workaround

BorisKamp Aug 4, 2014 11:57 PM (in response to 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!
Like Show 0 Likes(0)

Actions
9. Re: Multiple relationship per table workaround

philmodjunk Aug 5, 2014 7:33 AM (in response to BorisKamp)

     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.
Like Show 0 Likes(0)

Actions
10. Re: Multiple relationship per table workaround

BorisKamp Aug 6, 2014 4:13 AM (in response to 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!
Like Show 0 Likes(0)

Actions
11. Re: Multiple relationship per table workaround

philmodjunk Aug 6, 2014 9:31 AM (in response to BorisKamp)

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.
Like Show 0 Likes(0)

Actions
12. Re: Multiple relationship per table workaround

BorisKamp Sep 1, 2014 5:11 AM (in response to 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!
Like Show 0 Likes(0)

Actions
13. Re: Multiple relationship per table workaround

philmodjunk Sep 1, 2014 6:46 AM (in response to BorisKamp)

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.)
Like Show 0 Likes(0)

Actions
14. Re: Multiple relationship per table workaround

BorisKamp Sep 1, 2014 6:57 AM (in response to 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.
Like Show 0 Likes(0)

Actions

1 2 Previous Next

Go to original post