Hi,
Could anyone please advise me as to whether it is possible to group by the first letter of an item name in a report?
I have a db containing electronic parts and would like to create a catalogue but as I enter new stock items I have to print the whole catalogue each time?
I would like to just print the section containing the new items.
For example if I add a diode I would just like to print the D pages. Likewise a resistor just the R pages.
What is the best way to go about making my catalogue and doing the updates?
Thanks and kind regards,
jB
Hi @britesc,
It sounds like you probably want to add a filter to your query so you can just select the pages that match “D*”. I suggest that you start by taking a look at a getting started guide like this:
In SQL, you can select a set of records based on a partial-match on a field, for example:
SELECT * FROM parts WHERE part_name LIKE 'D%';To further @qubit1’s answer you could use a parameter in your query so the letter would not be hard-coded, but entered each time, i.e. SELECT * FROM “parts” WHERE “part_name” LIKE UPPER(:letter) || ‘%’
Create a query in SQL mode and use the following query (adjust the table and column names):
SELECT LEFT( "table"."group_column", 1 ) AS "first", "table".*
FROM "table"
ORDER BY "first" ASC
In the above query, group_column is the column that you want to use the first letter of to create groups.
Create a report, choose the query you just created as the data source: set the report’s Content Type to Query and pick your query from the dropdown list.
Open the Sorting and Grouping view and choose the first field as a group. In the Properties of the group header, you can play with the settings, but to start each group (i.e. each group of products starting with a new letter) on a new page so that you can selectively print certain pages of your catalogue, set Forge New Page to Before Section.