I have a few questions for the relationships between tables/views etc.
13 February, 2014
Hi David I have a few questions for the relationships between tables/views etc.
We have many tables that store data and many that include lookups to other tables that provide categories, grouping codes.
Based on the entity relationship if I have 2 tables and make the lookup table the primary one I get records that include all lookup codes which may include ones that are not part of the data. When I reverse the relationship only lookup codes that are in the data table display as an option to filter a report when the lookup field is included.
I have a client that has a big view where there are multiple data tables joined and which also have joins to lookup tables. Is there any way to configure a view like this so that all lookup tables that are added as a filter show all the values in the lookup table even if there is no data record with some codes? The client would like this to be the case.
Second question. The same client setup this big view with about 20 odd tables and in about 5 of them there is a common field. When they built the view they included all the common fields � e.g. employee number. They then created several calculated fields in the view and used the common field for some of them but because these are not clearly identified differently they now cannot easily remove duplicate fields. I said to them that they only needed one employee number because it is a key field and is recorded in each data table. Is there a way to identify where each field comes from � the table name?
Third question. In version 7 do you have the capability to have calculated fields using another calculated field either in a view or report?
Thanks
Gary
We have many tables that store data and many that include lookups to other tables that provide categories, grouping codes.
Based on the entity relationship if I have 2 tables and make the lookup table the primary one I get records that include all lookup codes which may include ones that are not part of the data. When I reverse the relationship only lookup codes that are in the data table display as an option to filter a report when the lookup field is included.
I have a client that has a big view where there are multiple data tables joined and which also have joins to lookup tables. Is there any way to configure a view like this so that all lookup tables that are added as a filter show all the values in the lookup table even if there is no data record with some codes? The client would like this to be the case.
Second question. The same client setup this big view with about 20 odd tables and in about 5 of them there is a common field. When they built the view they included all the common fields � e.g. employee number. They then created several calculated fields in the view and used the common field for some of them but because these are not clearly identified differently they now cannot easily remove duplicate fields. I said to them that they only needed one employee number because it is a key field and is recorded in each data table. Is there a way to identify where each field comes from � the table name?
Third question. In version 7 do you have the capability to have calculated fields using another calculated field either in a view or report?
Thanks
Gary
Hi Gary,
If you would like to return all possible values from your lookup table, even if it doesn't match with the other table, you should use a 'full outer join' so that all results are always displayed.
In terms of removing duplicate fields, here is what I think is the best thing to do.
At step 2 of the view builder, double-click on each field and find out which tables they come from :
Then identify which field you want to keep, I suggest using the column in the central table (that joins to all tables).
Then you need to start removing the other fields (or simply rename them), however you will need to ensure that the fields are not used in any existing reports or calculations.
To do this, just check the 'Usage' tab of all the fields:
Hope this covers everything and please let me know if you require more information.
Regards,
David
If you would like to return all possible values from your lookup table, even if it doesn't match with the other table, you should use a 'full outer join' so that all results are always displayed.
In terms of removing duplicate fields, here is what I think is the best thing to do.
At step 2 of the view builder, double-click on each field and find out which tables they come from :
Then identify which field you want to keep, I suggest using the column in the central table (that joins to all tables).
Then you need to start removing the other fields (or simply rename them), however you will need to ensure that the fields are not used in any existing reports or calculations.
To do this, just check the 'Usage' tab of all the fields:
Hope this covers everything and please let me know if you require more information.
Regards,
David