This page follows on from Setting up a form page.
At the moment, if we want to enter information from a food packet with two columns (say per serving and per 100g.) then we need to enter two records which involves duplicating some information.
So, how do we avoid this duplication? We want to add an other table as follows:
Notes:
- Instead of using ID in product table it would be better to use barcode as the unique key. Some existing entries don't have a barcode, although all new entries will have a unique barcode. Therefore we need to replace any empty barcode values with a unique but not not valid pseudo-barcode value.
- per will be one of: 'per serving','per pack','per half pack', 'per 100g','per portion', etc.
- if 2 is 'per portion' then weight is the weight of a portion.
Now we can have upto 2 'byWeightQuantities' entries for each product entry, but how do we convert the information that we already have in the database?
I guess we could convert the existing table back to a CSV again (this time including ID field), make a copy of this file, then edit the two files so that one only contains the 'product' information and the other only contains the 'byWeightQuantities' information.
But how do we keep the two tables linked?
The only way I could think of doing this was to copy the table back into the Calc spreadsheet program and then split the table and copy it back, this is just the way I found of doing this it might be possible to do this completely in base?
Anyway I copied the table
and pasted it into calc:
I then put values into the empty 'barcode' fields so that it can later be used as a key index. I just used 't' followed by the ID number, where the product is the same, I used the same 't' value:
I then saved two copies of this spreadsheet, one for 'byWeightQuantities' and one for 'product', we can then edit the two tables separately.
Modifying product table
In the 'product' version I deleted the ID column:
and also deleted the energy to sodium columns:
Then, where there are 2 rows for a product, I deleted the second entry sot that there is only one entry per product.
Also, in the description field, I removed any information that applies to the 'byWeightQuantities' table such 'per 100g'. This leaves the product information:
Modifying byWeightQuantity table
In the byWeightQuantity table delete 'ID' and 'type' columns:
and delete the 'hydrodised' column:
We then change the 'description' column to 'per' column and delete all text apart from the 'per' quantities.
Copy and paste back into Base
We can now select all the cells in Calc and copy+paste back into Base. We separately copy back the byWeightQuantities and product tables in a similar way we did for importing. When copying back the product table don't select 'Create Primary Key' as we want to use barcode as the primary key.
The final step is setting barcode to be the primary key in the product table.
Right click on the left hand cell next to barcode in product table design, set primary key:
When I tried to save this I got the following error message:
pressing more gives:
This means that some of the entries in barcode are duplicated, I therefore had to go back to the speadsheet, remove the duplication, and re-import it.
Next stage - go on to: form for many linked tables
Other sections are:
Data:
This site may have errors. Don't use for critical systems.