Is it possible to trigger a view query using a filter table?

[isahillioglu]

Hi,

I am trying to design a form that allow me to see a list of a stock information when I select a production record. Please allow me to describe my objective briefly so that you can give me your best.

Product definition

A product (electronic PCB in my case) consists of many components and each product may have one ore more of a specific component. For example a PCB may have 5 of the same component; say a resistor or transistor. So a product table holds a record of needed parts and how many of each.

Production definition

A production table holds an ID, the timestamp of the production date and the quantity of the production.

Stock

As usual, the stock stores the records of the available components for production.

In the form I want to select the date of the production so that it supply the production fields to the subform and I show them. Then I use another filter table with a subform of this subform in order to apply the production quantity to a view table so that in the view table some calculations are made to see what parts and how many of them are missing in stock for the relevant production.

I can filter the production successfully but I cannot trigger the view table query using a filter table. The last filter table is used to hold the production quantity value and is used in the view table to calculate the remaining stock after production and also missing quantity of components (if any). But the production quantity is not loaded to the filter table, hence the sql query cannot make any calculations and show me a summary list.

I think I better upload a copy of the file so that you all can see the every detail you need. I am using the embedded HSQLDB. Unfortunately all names except the form names are in Turkish, sorry for this because changing the names of the tables and field would imply to update everything. So I translated only the form files. Please excuse me for this. Here is the odb file:

PCB-Inventory.odb (411.7 KB)

Edit

Here is an annotated screenshot in which I describe my goal:

Please instruct me how could I done this correctly or better, let me know if this can be done in another way.
Thanks in advance.

[Villeroy]

Work-around: instead of the view, use SELECT * FROM “View”

[Villeroy]

PCB-Inventory.odb (420.9 KB)
This might work, if your view would return any data.

[isahillioglu]

Hi @Villeroy!

Thank you for your response. However I couldn’t enter any value into the FLT.INT1 text box in the view_hkbmk5_assembly form you added. So I could not see the result of your suggestion.

I thought that what I want can be done using a macro. So I tried to write a macro whose task is to just put the ProductionQuantity value into the filter table called tbl_hkbmk5_uretim_stok_suzgec using the dataset that already presented in the form when the context of the UretimMiktari text box is changed. Then I read the 2nd field’s value and update the UretimMiktari field of the tbl_hkbmk5_uretim_stok_suzgec filter table. As a matter of fact, the tbl_hkbmk5_uretim_stok_suzgec is not a filter table but just a table that holds the ProductionQuantity (UretimMiktari) value so that the sql query in the view table do required calculations based on this value. Here is what I did with macro so far:

Sub Main
Dim oDoc As Object
Dim oDrawpage As Object
Dim formSelectProduction As Object
Dim formHkbmk5Production As Object
Dim formProductionQuantity As Object
Dim formStockSummary As Object
Dim uretimMiktari As Integer
oDoc = thisComponent
oDrawpage = oDoc.drawpage
formSelectProduction = oDrawpage.forms.getByName("FormUretimSec")
formHkbmk5Production = formSelectProduction.getByName("FormUretim")
formProductionQuantity = formHkbmk5Production.getByName("FormUretimMiktari")
formStockSummary = formProductionQuantity.getByName("FormStokBilgisi")

uretimMiktari = formHkbmk5Production.getLong(2)
formProductionQuantity.updateInt(2, uretimMiktari)
formProductionQuantity.updateRow()

formProductionQuantity.reload()
formStockSummary.reload()
MsgBox formProductionQuantity.dbg_properties
End Sub

I’m writing this macro by referring to the Base’s guide, because I don’t have enough experience with Libreoffice macros. But I so code in several languages like C, Java, Swift, bash etc. Unfortunately this macro did not help either. I’m still playing with it to see if I get it working for the purpose finally.
Any help, tip or instruction that would direct me to the right direction is highly appreciated.

[Villeroy]

My bad. Open the filter table and replace the ID 0 with ID 1.

[isahillioglu]

Fixed it but the table shows nothing when I press OK button.

Never mind @Villeroy I finally figured out how to link the data field with the view table by trials and errors. It is important to understand well how base links the fields from master forms to the sub forms. Now that I am in the trusted user level and can upload more than one image, I will show soon how I linked the fields to reach my goal.
Thank you very much for your help.

[Villeroy]

The view does not show anything, even when unfiltered.

[isahillioglu]

Yep, because I used the filter table instead. This why nothing will happen in the view table unless the field UretimMiktari in the filter table has a value. But that filter table is now obsolete. I coupled the tbl_hkbmk5_uretim directly to the view table both in the view table and the review form. This how I’ve got it working as per my requirement. This way I don’t even employ a macro. Here I drop a working copy for you to see that has the changes what I mentioned.

PCB-Inventory.odb (411.0 KB)

Now the next adventure for me is to find out how to get parts out of stock based on the production quantity upon pressing a push button in the same form.