Cannot get some infomation into table via form

I am using Libreoffice base on Windows 10. As you can see on the attached file, I am trying to set a simple inventory input, say you are bying some stock and you put it in (which would later get out via invoice or so). The idea is to get a stocklist done in query by substracting outs from ins.
So I am entering information via a form to put items in stock.
My problem is that I cannot get some information to show up on the “Stock_IN”. I originally though that most columns could be set to integer in thet “Stock_IN” table but it pbviously ain’t like this.
See the attached file, I have managed to get the SKU information to show up by setting this to text but there is absolutely no way I can have the ITEM_ID to show up in the “STock_IN” table no matter what the setting of that column.
Does anybody have a hint on what I a doing wrong here? I have attached a file to better show the problem.TEST_STOCK.odb


It is somewhat obvious the direction you are starting in. I offer this caution. You will be in a much situation if you plan out this entire process first on paper. If you do not, you will find yourself going back and possibly re-doing the entire project multiple times.

Just in this process, how are you going to handle stock_in and orders_out for quantity verification? What about return items? When are quantities updated? There are bunches of more questions to be answered.

For your immediate question, changed ITEM_ID in Stock_In to an Integer. Then created a relation to ITEM_ID in ITEMS. Next created a form ( ITEMS_IN_Modified ) with ITEMS as a main form (using table grid control) and Stock_In as a sub form (using individual fields) with the link being ITEM_ID. Don’t see any need to keep SKU in the Stock_In record. Can always get this using SQL.

Just select an item in the grid and then the stock can be entered (position on a new record when needed) in lower section. There are many ways to present all this. This is just one such method.

Sample ---- Test_Stock_Modifed.odb

Dear Ratslinger,
many thanks for your valuable answer. Yes indeed I need to find a more solid way to appraoch this stock situation. I am not sure however how this will translate, so far I assume that you suggest to have a fixed stock list in form of a separate table, is that correct?
it has been many trials an errors so far but it is also part of the learning curve isn’t it? yes I would like to get a simple result for invoicing and stocklist for my own personal use but I also like to learn a bit more about SQL and the power behind Libreoffice Base in general.
Again, thank you for your very valuable support, I will now study your attached work further.


This should not be a Trial & Error process. This may be true for learning a process or function but not for a system design. Your system should be set based upon what the end result will be. Then it is a matter of how to get to that point. Starting from another point (like Stock) does not allow you to fully understand what all is needed for the final product since you have nothing else.

Unfortunately most start without a complete or even near complete plan and most give up part way through. So no, I am not stating you need anything specific except for a plan.

If I have not presented this post before, here is a list of many links on Base documents and macros → To learn LibreOffice Base are there introductions or tutorials?

Many thanks for the comments. Yes you are right, I must point out that I have started the process around the invoice with a customer and an items table and have build an invoice first. Now I have reached the point where I am happy with the Invoice so I wanna make sure that I can get a stock situation that will work. Hope that this makes sense.
Thank you for your link, I will indeed have a break and will continue to learn about base beforer going any further. And many thanks for baring with me here, I have been using Libreoffice for years, mainly writer and Calc and I only start to sense the power behind it with Base, absolutely amazing software suite!