Before I hand out useless advice based on my own assumptions about how your database is organized, let me ask if you're using something like this as your table structure:
In this structure, InSeq1 is the primary key for your Inventory table, ProSeq1 for the Products table, and BeSeq1 for the Beings table (the place where you store the name and contact information for your suppliers and customers). InOrOut can take on either of 2 values, "In" for products received from a supplier or "Out" for products sold to a customer. Unit Price would be auto-entered at the time an Inventory record was created, and Value would be calculated as Quantity * Unit Price.
So, for any given product recorded in the Inventory table (say "blue widgets"), you'd have a bunch of "In" records that would drive the summary total for blue widgets up and a bunch of "Out" records that would draw it down again. Stock of blue widgets on hand would be simply the sum of all the "In"s minus the sum of all the "Out"s that shared the ProSeqI code for blue widgets.
Is this the sort of model you're using? (Bear in mind that this is something I cobbled together for screenshot purposes in less than 15 minutes, so it leaves out a whole lot of detail and sophistication, but is it the same general idea?)
basically, similar but I think I have over complicated it the process.
so the concept I have been using is
-Link Delivery, Storage, Preforming in a ID relationship
-Data is entered into (Delivery table) E401 and then Summarized in SummE401, I then use a global storage option in E401Calc. I use same process for (table prefoaming)
-I then use (Storage Table) I set E401 to a calculation field that is as follows
-(E401 Storage Calculation) Delivery::E401Calc-Prefoaming::E401 Copy
I am fairly certain I have over complicated things but it does work for what I need at this point.
any advice to revaluate my process and convert it to a more simple solution will be appreciated.
1 of 1 people found this helpful
It appears to me you have set up the relationship incorrectly. Each table should have its own uniqueID field, which you have in all three tables shown—which should always be a unique value. However, to create a relationship from one table to records in another table—in your example, from Delivery to Storage—you need to have an additional foreign key field. So i this example, you would have field called, say, fk_deliveryID, into which would be entered the ID value from the Delivery table to which a Storage record is related. By matching as you have the primary ID in each table you will only ever get one record to match, whereas if you use Delivery::ID = Storage::fk_deliveryID, you will get as many storage matches as relate to the foreign key. This is because fk_deliveryID does not need to be unique.
Looking at your list of field names it appears there are other issues too, but the above addresses only the fundamental one of getting relationships working.
Okay that makes more sense, I will give it a try and see how it works out.
all advice at this point for me is good advice.
also note I'm trying to create live feed of data into storage that doesn't require the event of creating a new record.