Easy method for showing text, not IDs in reports?

[smeelah]

Hi,
Somehow, I have managed to create a database with a main table and 4 related tables with drop down lists;)

The issue is that the report is all numbers. Is it possible to just use the text names from the beginning or a way to have the report translate the ID numbers into text for the report?

I have to say that I'm not even sure what purpose the numbers serve...I was just following a tutorial which didn't explain. And I don't understand most of the terminology that is used here.

Btw, I am unable to install LibreOffice on this computer atm --- I am using an older OSX for some vital older software. Usually I just create spreadsheets but have been wanting to learn databases.

Thanks for any help!

[FJCC]

It is hard to say what you need to do without knowing more about what is in the database. Can you upload it here? To upload a file, click Post Reply and look for the Attachments tab just below the box where you type a response. Uploading the file will only be useful if you are not connecting the Base file to an external data source such as a standalone database.

[smeelah]

Here it is, thanks:)

[FJCC]

I has been many years since I used forms and reports in OpenOffice, so I can give you only limited help.
Here is a query that returns all the information in your main table but with the ID numbers converted into the corresponding text in the linked tables.

Code: Select all

SELECT "M"."SPSPMID", "P"."Product Type", "Sh"."Shop", "M"."Value", "M"."Date", "M"."Notes", "Mn"."Month", "Seas"."Season", "M"."Year" 
FROM "Sales Per Shop Per Month" AS "M" 
INNER JOIN "Product Type Table" AS "P" ON "M"."Product Type" = "P"."Product Type ID" 
INNER JOIN "Shops Table" AS "Sh" ON "M"."Shop" = "Sh"."Shop ID" 
INNER JOIN "Month" AS "Mn" ON "M"."Month" = "Mn"."MonthID" 
INNER JOIN "Season" AS "Seas" ON "M"."Season" = "Seas"."Season ID"

Click the Queries icon in the left Base pane and the select Create Query In SQL View. Paste the above code into the blank query and run it to see the result. Is that the sort of display you want?

[smeelah]

Thank you so much. Yes, that's what I was looking for and now I understand it more.

This is basically putting the information into a spreadsheet format, I guess. I had imagined that once I had a database set up, I could sort and display the data in a number of ways without too much effort but now I am thinking that it will require much more knowledge.

[FJCC]

Yes, databases are much harder to learn and use than speardsheets but very much better in many ways. You actually made a very good start if that is your first database. There are some suggestions I would make if you are going to continue working on it.

[smeelah]

Thank you for the encouragement! I would love your suggestions.

[Mountaineer]

... unable to install LibreOffice on this computer atm --- I am using an older OSX ....

For the MacOS 10.12 your signature shows, the latest possible Version is 7.3 wich can be found in the archives linked from the download page.

https://wiki.documentfoundation.org/Doc ... quirements

[FJCC]

None of this is a big deal.
1. I don't see the need to have a MonthID column. The table structure of an ID and separate columns of text values is used when when you want to consistently correlate to an entity but some of the details about the entity may change. So, you give a person an ID and have separate columns for the name and perhaps other facts. The name may change but the ID does not. But month names are not likely to change. You can still have a table to limit the values entered into forms but you can use a combo box and directly enter the month.

2. Is the season completely determined by the month? If so, I would add a Season column to the Month table and drop the Season table. Otherwise, there is a danger of the month and season not matching.

3. I would name the columns in the main table that store ID values to match the ID column in the linked table. So, Sales Per Shop Per Month would have a column named Product Type ID, not Product Type and the join condition would be

Code: Select all

ON "M"."Product Type ID" = "P"."Product Type ID" 

That is clearer in its intent, I think.
If the names match, in some database engines (not the built-in one for Base, I think) you could write

Code: Select all

USING "Product Type ID" 

4. I would not have spaces in table and column names; use underscores instead. You can often get away with skipping the double quotes around names if they have no spaces or special characters. Lazy of me, I know.

[smeelah]

... unable to install LibreOffice on this computer atm --- I am using an older OSX ....

For the MacOS 10.12 your signature shows, the latest possible Version is 7.3 wich can be found in the archives linked from the download page.

https://wiki.documentfoundation.org/Doc ... quirements

Thank you! That's great to know.

[smeelah]

None of this is a big deal.
1. I don't see the need to have a MonthID column. The table structure of an ID and separate columns of text values is used when when you want to consistently correlate to an entity but some of the details about the entity may change. So, you give a person an ID and have separate columns for the name and perhaps other facts. The name may change but the ID does not. But month names are not likely to change. You can still have a table to limit the values entered into forms but you can use a combo box and directly enter the month.

Yes! It was just a matter of wanting a drop down. And thank you for very helpful the ID explanation.

The season is just a back up because sometimes I receive payments that cover a few months...still working on how best to organize that info.

Currently trying to get my head around Parts 3 + 4. I'm sure I can do it. Thank you so much.