Displaying Invoice Items ordered by Item Group
Displaying Invoice Items ordered by Item Group
Dear members I have a challenge related to the appgini invoice and can't find an answer on the community forum yet. I have added a field to the items TABLE called item_group. This field is a lookup from a TABLE called item_groups. In the item_groups TABLE I have Review, Consultation, Admission, Laboratory, Medication and so forth. I would like to have the invoice items appear on the invoice ordered by their item group in the order of Review, Consultation, Laboratory, Medication... How can I achieve this?
Re: Displaying Invoice Items ordered by Item Group
Maybe it works the following way:
- add a new integer field `position` to `item_groups` table
- add a new calculated field `item_group_position` to `items` table
SQL for calculated field (untested):That new field should add the position of the group to each item.Code: Select all
SELECT `position` FROM `item_groups` WHERE `items`.`item_group` = `item_groups`.`id` AND `items`.`id` = '%ID%'
- In your project, change "Default sort by" of `items`-table to "Position"
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Displaying Invoice Items ordered by Item Group
Hello jsetzer, thanks for the prompt response. I have tried out what you suggested but it has not worked. The SQL of the calculated field yields nothing!
Re: Displaying Invoice Items ordered by Item Group
Then fields are missing or you have an error in your SQL query, perhaps table- or field-names are different
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Displaying Invoice Items ordered by Item Group
Dear jsetzer, please see the image attached for the tables in question. Thanks
Last edited by dennisans on 2021-06-17 10:19, edited 1 time in total.
Re: Displaying Invoice Items ordered by Item Group
OK, fields are there, SQL seems fine for this setup.
Please show us configuration and SQL of calculated field `item_group_position`
Please show us configuration and SQL of calculated field `item_group_position`
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Displaying Invoice Items ordered by Item Group
Dear jsetzer, please take a look at the config and SQL for calculated field. Also you suggested that I change the Default sort by in items table to position, but there's no position field in the items table!
Re: Displaying Invoice Items ordered by Item Group
Sort items table by column named 'item_group_position'.
The dropdown for selecting the sort-by column should show the captions of the available columns, not the names. I didn't know the caption you gave to item_group_position column. Now I can see from your screenshot that you have not named it 'Position' but kept the default caption 'Item group position'. Select that entry for sorting items.
The dropdown for selecting the sort-by column should show the captions of the available columns, not the names. I didn't know the caption you gave to item_group_position column. Now I can see from your screenshot that you have not named it 'Position' but kept the default caption 'Item group position'. Select that entry for sorting items.
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Displaying Invoice Items ordered by Item Group
I cannot say for sure, but for me the SQL I can see in your screenshot contains wrong quotations. I maybe wrong, but it looks as if you did non wrap table- and column-names in `backticks` but in 'single quotes'. Sorry, if I'm wrong.
Can you copy and paste the SQL here please. Please use [ code ] formatting for better readability.
Can you copy and paste the SQL here please. Please use [ code ] formatting for better readability.
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Displaying Invoice Items ordered by Item Group
I now have magnified your screenshot on my notebook (before I have seen in on smartphone, only) and this is what I mean:
(1) Those quotes surrounding table names and column names should be backticks. But to me those look like single quotes in your screenshot:
(2) Additionally the following looks weird, too:
It should be %ID% wrapped in single quotes, but it looks like double-quote, then %ID%, then single quote.
So, please see my posting above (2021-06-14 07:58):
If I didn't make a mistake, the quotation should be correct there
---
For SQL newbies
Maybe this short excerpt can help
(source: https://appgini.bizzworxx.de/support/troubleshooting/)
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Displaying Invoice Items ordered by Item Group
Code: Select all
SELECT `position`
FROM `item_groups`
WHERE `items`.`item_group` = `item_groups`.`id`
AND `items`.`id` = '%ID%'
Re: Displaying Invoice Items ordered by Item Group
Hi,
could it be that the item table is missing in the sql statement?
could it be that the item table is missing in the sql statement?
Code: Select all
SELECT `position`
FROM `item_groups` ,`items`
WHERE `items`.`item_group` = `item_groups`.`id`
AND `items`.`id` = '%ID%'
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Displaying Invoice Items ordered by Item Group
Yes, @pböttcher, you are completely right and I have forgotten to join the items-table itself. My mistake, I'm sorry
Let's try with this one
Let's try with this one
Code: Select all
SELECT `item_groups`.`position`
FROM `item_groups`
INNER JOIN `items` ON `item_groups`.`id` = `items`.`item_group`
WHERE `items`.`id` = '%ID%'
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Displaying Invoice Items ordered by Item Group
Thanks jsetzer and pböttcher.
This statement works and the calculated field is working as expected. Please note that am using the appgini invoicing system. In the invoice_items table which I changed to charge_items, I have added a field called position as a look up from items table. and made Default sort by BY of the charge_items table by position. As it stands all charge_items are displayed in the charge_items table in that order even when I keep adding other items they are all arranged by position. Challenge is, when I click the print Invoice button for the first time, everything displays in the order as they appear in the charge_items table. Then when I add more items to the charge_items table and click the print button to view the invoice for printing, the last added items display at the bottom of the invoice. And the order is no longer followed. It seems the print button resets the order on the invoice! I wish to have this order maintained.
Code: Select all
SELECT `item_groups`.`position`
FROM `item_groups`
INNER JOIN `items` ON `item_groups`.`id` = `items`.`item_group`
WHERE `items`.`id` = '%ID%'
Re: Displaying Invoice Items ordered by Item Group
Great, I'm happy that we were able to find a solution for your initial question (thanks @pböttcher for pointing me to the missing table-join).
For sorting-problem in printout I really do not have any idea because I am not using this at all. I hope others will be able to help you.
For sorting-problem in printout I really do not have any idea because I am not using this at all. I hope others will be able to help you.
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Displaying Invoice Items ordered by Item Group
Thanks pböttcher and jsetzer