Hi,

I have two tables, one is warehouse with all items I have and other one is called bill of lading. I am dealing with sellable items and demonstration items. It means, I send out and after week or two receive same items back in warehouse. I do not create invoices.

So far I did as follows:

in table warehouse I have fields catalog number, serial number, description, incoming date, and outgoing date. Based on incoming and outgoing date I have calculation field with simple result in or out.

in table bill of lading I have user information fields, product informations fields and select items field. This field is a value list filed with outgoing date. It means, if in table warehouse outgoing date has been filled out, this date will be shown in value list. Then in product information fields I will be able to select only specific product with specific SN.

Main goal of this database was, to be able to select only items based on serial number, which are scheduled to go out to specific customer. This is working just fine, but I am stacked and not able to get more information from this.

I try to get portal within warehouse table, which will show in one tab items still in warehouse and in other tab items which has left warehouse either as demo or sold items. Portals are very much related to the record which is open. Also, I can created relationship with calculation field which is set to "Do not store, recalculate when needed". Question is how to do it.

I'm not sure that I fully understand how you have structured your database - the items are in your warehouse table but some of the information about them is in your bill of lading table along with customer information. It seems that you need at least another table?

Perhaps split the Bill of Lading Table. Make a new table for products/items and add them to each bill as a line item...?