Hello all.
I have a report that works very well to show different transactions. My problem is, the table it is reading from uses an integer code rather than text to log what action occurred. I have a separate table that houses the list of codes and a text description. In this way, I save space when saved.
My challenge now is how to join the two tables when the report is run? I am using Shorts with the desktop designer. Not sure if there is a way to do this within shorts, or if a stored proceedure has to be created (and of course how to tell shorts to run it) or what? I am in now way an expert in Database stuff so would appreciate any direction/hints etc to solve this.
Thanks all!
Tim
Hi Tim,
Maybe you could tell us what database you’re using ? SQLite, MySQL, …
I’m not using Shorts so I don’t know if it’s possible to declare joins in Shorts, but most (if not all) SQL DB engines are supporting “views”. A View is virtual table based on the result-set of an SQL statement.
Once defined you can then query your view like a “normal” table… so probably use it right away in Shorts.
I see from Shorts documentation that views are supported.
In general you won’t have to ‘figure out’ the join on your own. If you drag the ‘join’ field onto the designer it will attempt to figure out the proper join for you using the database relationships. It will warn you if it can’t create the relationship.
To create the relationship manually (if Shorts can’t calculate it) you can use the Relations toolbar button or the Manage Relations in the Reports menu to create the relationship.
Alternatively, you can use a view as Olivier suggested.
As you have, Tables and Views, you could add “Queries” (BKShorts user would name them) to the list of source Fields for a future version. It should allow advanced users to create their own “Client side Views”, complete, with joins, unions, etc.
Thank you everyone!
I’m using SQLite right now. Will try what Bob stated first.
Thank you,
Tim
If you run into problems let us know.