Query for get consolidated data of particular item

[syp1977]

This is in continuation to post:
viewtopic.php?f=61&t=106632
I have made Two Tables for inventory managment:
Table 1# having fields Material_ID, Material_Name, Received_Qty
Table 2# having fields Material_ID, Qty_Utilized
Suppose, I am keeping inventory of different fruits each of unique lots. eg. If I have inventory of 5 different lot of oranges, two different lot of apples etc. Can we have query to see detail of only oranges incoming and outgoing and balance?
Thanks in advance,,

[chrisb]

by lots do you mean batches? i see no field for "lot".

did you try appending a WHERE clause to your code:

Code: Select all

where m."Name" = 'Pear' --specify material by name

open in Edit mode & look at 'qINFO_OriginalQuery' in the attachment.

your materials are now fruits so i will assume that you downloaded this demo & are looking for something similar.

there are two major differences between that demo & your db:
1) you use two tables for input/output.
2) you identify materials of the same type by lot/batch.

the attachment below contains four tables "tMaterial", "tMaterial_IN", "tMaterial_OUT" & "tFilter" (single record, two fields ID & "MaterialID", enables display/manipulation of unique material).
"tMaterial" has the fields "ID" & "Name", i moved them from "tMaterial_IN" because they do not belong there.

there is one form, it shows all essential & some less vital info.
the filter table sits at the top of the tree, below that are 5 sub-forms.
it's very clumsy to refresh 5 subforms manually so when the material in the list box changes a macro is fired which does the job for you.
remember this is a demo which shows how vital information may easily be extracted from a simple but properly constructed database.

NOTE:
the 'Current Stock' form is never auto refreshed.
all forms: you must hit the update buttons to refresh specific totals as items are moved in/out.

if you add a material then hit the 'Refresh List' button & a macro refreshes said list box.

the running total, not strictly necessary is a pain & requires 2 views with the embedded db so i have included a recursive query (one small query & zero views but it does require hsqldb 2.x).

used windows 10, libreoffice 6.4.4.2 (x64), embedded hsqldb 1.8.0.10

[syp1977]

Thanks Chrisb,
Is it possible to have pop-up to input Material Name "tMaterial".
I tried with putting ......
.....AND ( LOWER ( "tMaterial" ) LIKE LOWER ( :qInward || '%' ) OR LOWER ( "tMaterial" ) LIKE LOWER ( :qInward || '%' ) AND :qInward IS NULL ) at the end of given query. But it is not working. Any suggestions..

[Villeroy]

Drop
AND :qInward IS NULL
and see.

[chrisb]

@syp1977,
if the query you are using is 'qINFO_OriginalQuery' then from the 'Queries' pane:
drag said query a little & then drop it over white space.
you will be prompted for a new name, change the name & hit OK.
open the new query in edit mode & cancel direct mode by clicking on the tool bar icon 'RunSQL command directly'.
append this:

Code: Select all

where lower(m."Name") like lower(:MaterialName || '%')

then save & execute.

':PARAM' is a 'BASE' function & will not work in direct mode, unfortunately this means that you lose the formatting.

[syp1977]

Thanks chrisb and Villeroy,
It worked perfectly well.
Is it possible to put dropdown list for selection of Material instead of typing it?
Best Regards

[chrisb]

YES!
main-form with filter table as data-source & list box for input.
sub-form with query as data-source, table control for output & refresh button.
there is no need to link master/slave fields because the query will select the value of "MaterialID" from the filter table.

the attachment demonstrates this method with a pragmatic example.
i initially refresh the forms using a macro (because i was refreshing several forms) which is fired when the list box selection is changed.
some of the forms also need to be updated manually at times & do therefore contain button controls.

i guess that you're pretty new to databases (poor structure, bad notation, spaces in field names, etc.).
it's going to be very difficult for you to successfully develop this project.
you will need to examine the form in edit mode & view its properties using the form-navigator. forget about using the BASE wizards they are useless for something like this.

[syp1977]

Hi Chrib,
Thanks for answer.
Can you share such example ... Plz?
Regards,