The X operator matches any record in properties to all records in companies. But then a portal filter and/or a portal sort order might be specified to further modify what you see in the portal. These settings, however, will not affect what you get when you export your data.
Are you reusing id numbers?
Note, when using the X operator in your relationship, the values in the match fields are irrelevant. You can even delete the fields specified as match fields from the two tables and this relationship will still function.
On the properties side I've defined a unique field with serial number pkPropertiesID. The REF number in the companies table is not a unique number but functioned as the relation in the original database (via the X operator). There are multiple values of the reference so one property can have multiple companies (which are in fact contacts).
If the X operator causes the companies::REF field not to be needed, how can I convert this table to a normal one to many relationship (and use the = operator)?
I did not say that it was not needed. I simply described how the X operator functions. And I pointed out that the values in BOTH match fields will have no effect on what records appear in the portal or that are exported by your export.
I suggest that you start over and describe how you want this relationship to function. It's quite possible that the X operator is not the correct operator for what you need.
The goal is to have a one to many relationship, where one property can have multiple companies (contacts). In the companies table I've got multiple identical REF fields, because those multiple fields are linked to one property. This is done by design, exported from another database (oracle) which is not existing anymore. Therefore starting over is not an option.
Earlier I was trying to print a letter that contained data from the property record as well as from the portal to companies. This caused unexpected behaviour. It generated non corresponding data for the property record. That was solved when I changed the operator to = Unfortunately it also destroyed the data in the portal. When I've added a new line in the portal, the result was as it should be.
Hope this helps to define the issue.
I am afraid that the phrase "I've got multiple identical REF fields, because those multiple fields are linked to one property." confuses the issue greatly. You'll need to post a detailed explanation of what that means in terms of the actual data involved.
A one to many relationship linking One property to multiple contacts would be set up like this:
Properties::__pkPropertyID = Companies::_fkPropertyID
And if this is not what you have, it does not mean that you cannot modify your design to implement this change in order to get a proper relationship provided that there is some data in your tables that can be used to temporarily but correctly link records in order to make this design improvement to your database. (One that really should have existed in the Oracle database to begin with.)
I took a look at your database file. As I suggested earlier, the reason that your exported data differs from what shows in your portal is due to the fact that your portal has a portal filter. This filter will be ignored by your export.
From what I saw in a very preliminary check the expression that you have set up as a portal filter is what you should have designed as your relationship by dragging from Properties::REF_Tenant to Companies::REF and using the default = operator. You can then use an unfiltered portal instead of a filtered portal as well as getting the desired results when you export.
I was looking at the wrong field. Changing the relation to Properties::REF_Tenant = Companies::REF and removing the filter resolved my issue for creating the letter. Thank you very much!!Since I understand (and was trying to accomplish) that I should have a unique serial field for Properties::__pkPropertyID, I wonder how I should move forward. It crossed my mind to rename Properties::REF_Tenant to Properties::__pkPropertyID and defining it as a unique serial field, but it doesn’t contain unique data. So it doesn’t seem a very good idea.Actually, the field Properties::REF contains a lot of duplicates.
Properties::REF contains a lot of duplicates.
Then from here, it appears that you have a very serious problem with your data. That value MUST be unique in Properties or you cannot reliably match one record in Properties to many records in companies.
Yes I realise that. Do you have any idea how I should do that? Is this something that is possible by scripting? Or will it be manual labour? For over 6000 duplicates it's not that easy.
I think I that I see a way and what is going on here, but I need to check something.
What does that "ref" field in Properties really represent if it is not unique?
If I have the following two records in properties, for example:
Property 1, Tenant_Ref = 5
Property 2, Tenant_Ref = 5
And the following two records in Companies:
Company 1, Ref = 5
Company 2, Ref = 5
Is that intended to show that both Company 1 and Company 2 are linked to both Property 1 and Property 2?
The Properties reference (Properties::__pkPropertiesID) is a unique serial number. The REF field in Companies is being used to link one property to multiple "Companies" (or contacts).
A property has an owner (which can be more than one) and a tenant (which can be more then one). Both REF_owner as REF_tenant refer to the value in REF::Companies. If there are for example 7 owners, it links to 7 times the same reference in the table companies. I understand that it isn't probably a good idea to link to an owner AND a tenant to the same table. Therefore I need to create a table occurrence.
Later on I would also need to convert the relation properties to companies as a many-to-many (meaning another one to many relationship). This would make it possible that one company (owner or tenant) can be linked to multiple properties. And I hope this last sentence answers your question.
This doesn't answer my question.
You do have a _pk field, but you are not currently using it. Linking to owners is a problem we are not ready to deal with. The answer to my question is critical to whether or not you can even salvage the data in your table.
I need to know:
a) if there are multiple properties with the same value in tenant_ref. You've indicated that the "values are not unique", but it isn't fully clear that we are discussing this specific field in properties.
b) if there are multiple companies with the same value in ref.
If a and b are both true, you either have Chaos or this has the interpretation I have suggested in my preceding post, that you have more than one property linked to the same group of multiple companies. (One property can have many tenants and a tenant can rent more than one company but is this shown in your current data in the fashion that I am suggesting?)
a) Properties::REF_Tenant has multiple records for the same value. In a effort to correct this I started the __pk field (which isn't being used as you already pointed out).
b) yes and no; meaning there can be multiple records (with the same REF) but they all have the same company name. Since company records also have a portal to a table contacts, the difference between the records is caused by that. If the property points to 3 tenants (companies), there are 3 records with the same REF in Companies, but each record having another name in his portal.
In the current setup companies can appear in multiple properties.
To make it even more complicated :-)