The difference between the two data sets is that the top set makes use of a Lookup, which means that the table link needs to be maintained manually. the bottom set is a filter, so that the link is established automatically.
Q1, Is the difference the reason?
Q2. Is there a way to get the lower level info displayed in table format? I have tried several different permutations in the formula, and have not been able to get it working.
That was my idea as well, but I am not getting it to work, i have tried two ways:
One where I create a lookup, and then filter with a formula in the filter at the table level.
The second where I create the lookup, and then under lookup options build a filter.
My assumption at the moment is that lookup is an advanced version of the select list, i.e an assist to data entry.
I think so too, i have subsequently found that when you use lookup, you get an option in the detail table whether to display the details as a table or not. That option is not available when the link is a filter
What I have done is to group the lower table, which works okish.
What should be displayed in the table for GL Accounts?
Right now it’s just a set of numbers (the “Cost Element 1” from each linked GL Account). One option, if you prefer, is to format them as a BulletedList() if that’s easier to read.
If you’d instead like to see the other info related to each GL Account (besides just the Cost Element 1's), you can show column Link to GL Accounts as a table.
Orrrrr… are you saying that Cost Element 1's (like 50300000 for example) correspond to yet another table somewhere, with additional info attached to each of those? If that’s the case, column Cost Element 1 in table ccsLink to GL Account needs to be type Lookup instead of type Text.
You are using text columns instead of lookup columns and you have a very confusing and inefficient table structure.
Think about it this way. Each table is a list of rows with minimum needed information. This way, you should have more tables.
In your doc, there should be at least these (and probably other) separate tables:
Quantity Structure Types
Cost Component Types
Account Creators (or just Users)
P&L Statement Accounts
When you detangle all the info into these separate tables, you connect them by adding Lookup columns.
Eg. Cost Components table should have a column that is a Lookup on Clients table.
When you do this with all the tables, you will be able to create Row Layouts where you can for example for a given Client in Clients table, open the row and see a table with all Cost Components of that client.
(Oops, seems like I forgot to hit the final button.)
Thanks for the investigation.
Let me give little bit of background to the data: This data is used to set up the configuration of SAP. The cost component is a summary of costs. The cost component table has several characteristics, e.g. Cost Component Number, CC description, whether the costs are 100% fixed, or whether there is a variable component too.
The second table stores the GL accounts that make up a cost component. This table has the cost component (the key), the GL account number and the G account description. For example the cost component Labour OH, will consist of GL account Wages, Salaries, Perqs, Monthly holiday provision etc.
So the purpose of this Coda is to be able to go to a client, download those two table, and have a join that clearly shows the relationships.
I have settled for the Group view as shown below, but I think that using a table in the detail view looks better:
as I mentioned above, these two tables are input parameters to the doc, the purpose of the doc is to join them.
Lookup is an entry aid, but when the data already has been captured, it does not provide any benefit. (Or at least, I have not found a way yet to use it for that purpose.)
On the normalisation of data tables: Coda is not a relational database. It is not going to maintain your referencial integrity for you. It helped saving disk space, by eliminating redundant data from your database. That is no longer as big a concern, and a more important consideration in table design is ease of understanding and use.
The most pertinent example I have yet come across is SAP’s upgrade/rewrite of it’s ERP system from ECC-disk based R3 to the S4 RAM based. They went from storing financial entries in around 15 different tables, to a single Table.