Displaying Invoice Items ordered by Item Group

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
dennisans
Posts: 11
Joined: 2021-06-13 20:02

Displaying Invoice Items ordered by Item Group

Post by dennisans » 2021-06-14 00:45

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?

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Displaying Invoice Items ordered by Item Group

Post by jsetzer » 2021-06-14 07:58

Maybe it works the following way:
  1. add a new integer field `position` to `item_groups` table
  2. add a new calculated field `item_group_position` to `items` table
    SQL for calculated field (untested):

    Code: Select all

    SELECT `position`
    FROM `item_groups` 
    WHERE `items`.`item_group` = `item_groups`.`id`
    AND `items`.`id` = '%ID%'
    
    That new field should add the position of the group to each item.
  3. In your project, change "Default sort by" of `items`-table to "Position"
Check if the SQL of your calculated field works correctly by opening the items-TV and looking at the values of the new "item_group_position" column.
Kind regards,
<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 readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

dennisans
Posts: 11
Joined: 2021-06-13 20:02

Re: Displaying Invoice Items ordered by Item Group

Post by dennisans » 2021-06-15 14:35

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!

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Displaying Invoice Items ordered by Item Group

Post by jsetzer » 2021-06-16 18:48

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 [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

dennisans
Posts: 11
Joined: 2021-06-13 20:02

Re: Displaying Invoice Items ordered by Item Group

Post by dennisans » 2021-06-17 10:13

Item and Item Group Tables.PNG
Item and Item Group Tables.PNG (12.24 KiB) Viewed 3640 times
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.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Displaying Invoice Items ordered by Item Group

Post by jsetzer » 2021-06-18 05:00

OK, fields are there, SQL seems fine for this setup.

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 [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

dennisans
Posts: 11
Joined: 2021-06-13 20:02

Re: Displaying Invoice Items ordered by Item Group

Post by dennisans » 2021-06-18 07:07

Config.PNG
Config and SQL statement image
Config.PNG (139.88 KiB) Viewed 3590 times
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!

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Displaying Invoice Items ordered by Item Group

Post by jsetzer » 2021-06-19 19:53

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.
Kind regards,
<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 readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Displaying Invoice Items ordered by Item Group

Post by jsetzer » 2021-06-19 19:58

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.
Kind regards,
<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 readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Displaying Invoice Items ordered by Item Group

Post by jsetzer » 2021-06-20 09:00

jsetzer wrote:
2021-06-19 19:58
... it looks as if you did not wrap table- and column-names in `backticks` but in 'single quotes'lity.
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:

chrome_InmsStwQkw.png
chrome_InmsStwQkw.png (22.6 KiB) Viewed 3518 times

(2) Additionally the following looks weird, too:

chrome_DymwWTcu0C.png
chrome_DymwWTcu0C.png (178.3 KiB) Viewed 3518 times

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
chrome_luNGCszMhq.png
chrome_luNGCszMhq.png (17.47 KiB) Viewed 3517 times

---

For SQL newbies

Maybe this short excerpt can help

chrome_gb7J8AWUGg.png
chrome_gb7J8AWUGg.png (24.12 KiB) Viewed 3516 times
(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 [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

dennisans
Posts: 11
Joined: 2021-06-13 20:02

Re: Displaying Invoice Items ordered by Item Group

Post by dennisans » 2021-06-20 14:04

Code: Select all

SELECT `position`
FROM `item_groups` 
WHERE `items`.`item_group` = `item_groups`.`id`
AND `items`.`id` = '%ID%'
Dear jsetzer. Above is the code as I copied and pasted it into the project's calculated field.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Displaying Invoice Items ordered by Item Group

Post by pbottcher » 2021-06-20 15:11

Hi,

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.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Displaying Invoice Items ordered by Item Group

Post by jsetzer » 2021-06-21 08:47

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

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 [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

dennisans
Posts: 11
Joined: 2021-06-13 20:02

Re: Displaying Invoice Items ordered by Item Group

Post by dennisans » 2021-06-23 16:35

Thanks jsetzer and pböttcher.

Code: Select all

SELECT `item_groups`.`position`
FROM `item_groups`
INNER JOIN `items` ON `item_groups`.`id` = `items`.`item_group`
WHERE `items`.`id` = '%ID%'
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.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Displaying Invoice Items ordered by Item Group

Post by jsetzer » 2021-06-24 06:28

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.
Kind regards,
<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 readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

dennisans
Posts: 11
Joined: 2021-06-13 20:02

Re: Displaying Invoice Items ordered by Item Group

Post by dennisans » 2021-06-25 15:14

Thanks pböttcher and jsetzer

Post Reply