Summry field

[adityaverma]

Hi,

I need help to understand how can I export sub-summary field to another database. Let me explain with an example.

I have two databases , let us say Orders and Inventory. In orders database I have more than one record for same product and in inventory database I have one record for each product. What I am trying to find out is how many orders I have for this product and would like to compare with inventory I have.

Say Orders have

Product Units

1. Prod 1 5

2. Prod 2 1

3. Prod 1 2

when I use summary layout, I can see that Prd 1 has orders of 7 units and Prod 2 has rders of 1 unit but, I will be thankful if anybody can explain me how can I populate this (i.e. Prod 1 has order of 7 units) number in invventory database against prod.

Thanks in advance.,

Aditya

[LiveOak]

To answer you immediate question (although I don't think this is how you should handle inventory), you can export the contents of a subsummary field using the Export function. If properly setup, this will export one record per subsummary item (not one record per record in the current file).

What you REALLY should do is one of two things:

1) In a properly designed inventory system, you should use a transactions file to track every inventory event. If you sell 5 pieces of and item, you would create a draw transaction when the item physically leaves inventory. This could happen as part of the shipping process or as a stockroom draw prior to shipping. It depends upon how your inventory operation is physically organized. Likewise, when you receive inventory items, a stock transaction would be posted to indicate that the inventory count has been increased. In real inventory systems, this leaves an audit trail that can be traced to find an inventory count error. If you just keep inventory as a single number, errors are hard/impossible to find and trace.

2) The quick and dirty way to get sales quantity into inventory is to create a relationship between Inventory and Orders based upon Product Number (create this in the Inventory File). The total of a given product sold (I'm assuming a record in your Inventory file is a Product) is just a calculation field:

QtySold (calculation, number) = Sum(RelWithOrdersByProduct::Qty)

The field in Orders is just the Product/Qty, not a summary field. The Sum function uses the relationship by Product number to isolate and summarize the quantity across the entire file for the product number of the records you are on.

-bd

[Kurt Knippel]

Assuming that you can see that data as you want it in the other DB (i.e. you have the correct summary fields and the right sort orders) then you can EITHER export the summary to a seperate file OR setup summary calculations in the Products DB based upon the ProductID relationship.

If you want to export the summarized data (making it a static "slice in time"), then without changing you found set or sort order, go to the File menu and select Export Records...; save the file and move the fields that you want summarized (i.e. the SUMMARY fields) into the Field Order box. Now click the Summarize By button and select the order by which you want them summarized (i.e. ProductID). I would also make sure that you export the ProductID as well, so that you can match the summary up with the Product when you import them.

If you want to setup a dynamic summarization then you can use the SUM calculation function in the Products DB. It is setup as a calculation field, like this SUM (Orders by ProductID::Qty). Although in this case you may need to add some additional criteria so that you only get the SUM for a certain time period or unfullfilled orders or whatever..