set report problem

[vasant panjabi]

Set for material inventory � `mtrl_set`
Table: mtrl_in (primary) & mtrl_out (child)
linked by mtrl_in_no & mtrl_out_no (one to many).

Each inventory item `mtrl_in_no` has specific number and `mtrl_out_out` uses same no.
Example: mtrl_in_no: 1a�`qty_in` - 100 pcs (one record)
Mtrl_out_no: 1a-`qty_out` - 50 pcs / 10 pcs / 30 pcs (3 records)

Report: on mtrl_set : report calculated field:
ttl_qty_in: total(qty_in,grp->inventoryin)
ttl_qty_out: total(qty_out,grp-nventoryout)
balance: ttl_qty_in-ttl_qty_out
result: ( 100-90) - balance =10
this works fine

problem: in case a new item added `SAME INVENTORY` in table: `mtrl_in`
example: 2a � 300 pcs
and it does NOT have even ONE qty_out record for 2a.
total(qty_out,grp->inventoryout)
evaluates to `0` (zero)
it just ignores qty_out figures of 1a.
and balance shows as: 400 (when it should show as : 310).

If create one dummy record for 2a in mtrl_out with qty: 1 then the total of qty_out is generated correctly. (309 )
(qty_in & qty_out are required fields in field rules)

would appreciate if can point out what am I doing wrong. Have tried linking with inventory but the report generation goes into long calculations & about 450 pages are generates as there are over 2000 records in `mtrl_out` & over 50 inventory items but the problem is same while with link as made the report is instant & decent 80 pages

Rgds,
v.p

[Tom Cone Jr]

RE: set report problem

V.P.,

In the typical one-to-many set there is only one record in the parent table which links to the related child table records. Your description suggests to me that perhaps you are storing more than one 'inventory_in' record for each item in your parent table. Instead of a one-to-many set, which your report is expecting, you are storing records as though the set had a many-to-many link. If this is what you are doing then that probably explains the behavior you are seeing.

Consider rearranging your set design. I can think of two approaches:

1) Use three tables. One, which will be the parent in your set, contains descriptive information about each inventory item, but no quantity information. It could be linked to an 'inventory_in' transaction table where you track additions to each inventory item, using the inventory item part number as the link field. A third table would be used for the 'inventory_out' transaction table, where you track the usage of each inventory item, again using the part number as the link field back to the parent table. One parent table, two child tables each linked one-to-many to the parent. No grandchild tables.

2) Use two tables. One, which will be the parent in your set, contains descriptive information about each inventory item. Each item is given a unique inventory part number to distinguish it from the other items. A second table would be used to track both additions and usage of inventory items. It would be linked one-to-many to the parent table, using the part number field as the link. It would contain a quantity field, and a transaction_type field. The transaction_type field would be 'In' if the transaction was adding items to the inventory, and it would be 'Out' if the transaction was removing items from inventory.

I think the second scenario would be easier to work with than the first.

-- tom

[Stan Mathews]

RE: set report problem

"example: 2a � 300 pcs
and it does NOT have even ONE qty_out record for 2a."

Yes, it does have one qty_out record, in the set. By definition, each parent record in a set creates a "set composite" record. It's just that all the fields in the child table for this record are blank. I think this is what is causing your troubles, because this blank value is matching other records incorrectly.

Did you try creating a real child record with a qty_out of 0? You may have to code this into your application to get the calculations to be correct.

[vasant panjabi]

RE: set report problem

dear tom/stan,
sorry for delayed reply - was out of town.

in the parent table the entry is specific - only one unique mtrl_in_no for every incoming inventory. however,
the mtrl_out_no in the child may be many.
to me this sounds like one to many.

anyway, what stan suggested to make a record in dummy with zero values (in case there is actually no inventory out) worked.thanks stan.

i really wonder why made it in a set when a single table for in & out could have been okay.

thks & rgds,
v.p

[Tom Cone Jr]

RE: set report problem

VP,

Sorry if I misunderstood your first post.

You described a set with two tables, mtrl_in and mtrl_out. Because your calculated field for 'Balance' seemed to group records in both tables, based on their inventory id number, I assumed you would have multiple materials transactions in each table for each item in your inventory. That's many to many.

-- tom