FileA Summary fields seen in a portal in FileB

[Ugo DI LUCA]

I want to use the result of summary fields from my Line Item into a portal for Inventory in my Product File.

The Products I'm selling are subcategorized (think of a validity date attached to a product). So I want to list all the category in stock for each product.

Using selfjoin, I can select any category from a popup list and see the detailled records for each, but for a more dynamic viewing, I would like to get a "list" of all categories in stock portal (I just need 5 lines that is the max category I hold in stock for the same article).

I thought about a portal, as it can immediately display these records, but cannot find a way to catch the summary fields from Line Items into that portal.

I tried using ProductFile:Product_Id::LineItem::conc_Product_Id&Categ

where conc_Product_ID&Categ = Product_Id & "P" & Categ.

Doesn't work.

Any ideas. Thank you

[Razumovsky]

Do you have an Inventory file as well (3 files total)?

From your post, I think that in your ProductFile, you are trying to display a total of in stock items from inventory file minus the number sold in LineItems for each subset of a given category. Is this close?

If so, try creating a 2 calc fields in Inventory:

c_RemainingStock:

#InStock-Sum(Prod_IDLineItems::#Sold)

c_CategoryRemainingStock:

sum(CategorySelfJoin::c_RemainingStock)

use c_CategoryRemainingStock in your portal.

I dont think this is exactly what you need, but might help trigger the solution. If not, could you post a little more detail?

HTH

[Ugo DI LUCA]

See this thread and attachments

This is my test file. Please note that this version has been updated as I finally found a way to make it totally relational, thanks to a "automatic lookup field".

I cannot send it though because it is actually on another computer, but I will post it to you if you want.

Still, I cannot get the solution to this current post. In the product file (where the inventory actually is-no 3rd file), you will have 3 options to view the current stock count (quantities and costs) :

- One that actually give you the current stock for the Product_ID (with all movements from the Lines Item in a portal)

- Another that actually summarize the movement from stock by shade/calibre (this is the subcategory) for the same Product_ID, showing the "history" of operations from the Line Items summarized by Shade/Calibre.

- Last one that actually only show the calibre/shades actually in Stock (residual qty>0) and the operations from the Line Items for this "in stock Shade/Calibre".

At that point, I can filter the records using a value list. But I would like to have these values in a portal...

This is my first Inventory File, so please tell if there are major problems to have the Inventory in the Product File. I found it very "relational" and cannot see so far any cons for that solution.

Thank you so much for your help.

[Razumovsky]

Okay, I skimmed the other thread, but could not download the attached test file (I have downloaded succesfully from the site before- did you compress it?)

The terminology is getting a bit confusing for me, let me try and summarize and see if I got it:

your tiles have 4 identifying fields, only the last is unique to a tile.

name

shade

calibre

UniqueID

In the products file, you want to view a portal of only in stock shade and caliber combinations for a given name, and list the activity (#sold and # on order) from line items for these combinations.

Is this right?

- One that actually give you the current stock for the Product_ID (with all movements from the Lines Item in a portal)

- Another that actually summarize the movement from stock by shade/calibre (this is the subcategory) for the same Product_ID, showing the "history" of operations from the Line Items summarized by Shade/Calibre.

- Last one that actually only show the calibre/shades actually in Stock (residual qty>0) and the operations from the Line Items for this "in stock Shade/Calibre".

are you having problems with all 3 of these, or just the last one?

Feel free to contact me privately.

[Razumovsky]

Cant seem to email you the file, so I posted it here for download. Is this what you need?

edited attachment- forgot to add a combination to the multikey-

TileProducts3.zip

[Ugo DI LUCA]

No.

I'm sorry you spent some time with this attachment.

However, this will probably be helpful for those who needed results from Multikey. [color:"red"]Hope you'll got a second star for your constant help and for this one as it is very clear.

I didn't mentionned it but I use Multikey to filter my Product File where products have this description :

MyProduct_ID - Category Abbreviation - Collection Name - Color - Size - VendorProduct_ID

Therefore, I have :

- 4085 - GC - Via della Seta - Oro - 30x30 - 4fgk5

- 4096 - GC - Via della Seta - Beige - 15x15 - 5gkl2

- 4098 - GC - Via della Seta - Yellow - 30x30 - 3flm4

There are already 20,400 records in the Product File. That is why I do not want to use a different ID for the product as this is always the same product, and I have already a huge Product Db. This will expand the number of records and wouldn't be helpful.

I do not understand why you couldn't download my attachment, but I'm sure you'll get what I'm trying to do.

I have a Product File and a Line Item. The Inventory [color:"red"]can be viewed in my Product File , using 3 relationships to the Line Items and 3 portals :

- Relation 1 : Product:Product_ID::Line Item:Product_ID

- Relation 2 : Product:c_KeyProduct_IdCalibreShade::Line Item:c_KeyProduct_IdCalibreShade

[color:"red"] CalibreShade is a record from the line item , I use [color:"red"]Conditional Value List to match this field in order to create c_KeyProduct_IdCalibreShade in the Product File.

- Relation 3 : Product:Product_ID::Line Item:c_Keylookuprelationfield

Where c_Keylookuprelationfield is a lookup field [color:"orange"]Thanks to Bob Weiver in Line Item used to index the Calculation Case(ResidualQtyperCalibreShade* > 0; "Product_ID"; "")

*

ResidualQtyperCalibreShade = Tot_RelatedPO_CalibreShade - Tot_RelatedINV_CalibreShade

Tot_RelatedPO_CalibreShade = SUM(SelfonCalibreShade::Quantity PO)

Tot_RelatedINV_CalibreShade = SUM(SelfonCalibreShade::Quantity Invoiced)

with SelfonCalibreShade = Line Item:c_KeyProduct_IdCalibreShade::Line Item:c_KeyProduct_IdCalibreShade

- Portal N

[Razumovsky]

Dont worry about the attachment- I suspected that was too simple...

Some questions:

For the moment, I can see 150 products "Via della Seta Oro 30x30 4fgk5" in my stock, and then I must select each shade/calibre to check the remaining quantities per shade.

Where can you see this? Through a portal? Based on Which Relationship?

I would prefer having a portal that would display :

Via della Seta Oro 30x30 4fgk5 with cal/shade 4c2 - Qty remaining = 25

Via della Seta Oro 30x30 4fgk5 with cal/shade 4d3 - Qty remaining = 40

Via della Seta Oro 30x30 4fgk5 with cal/shade 2b4 - Qty remaining = 10

Via della Seta Oro 30x30 4fgk5 with cal/shade 3b1 - Qty remaining = 25

What exactly is the Calc for Qty Remaining? is it RealStock_Product_ID = Qty stock_ProductID - Qty reserved_ProductID?

By the way, what do you personnally think of a non separate Inventory file

Hard to say, seeing as it seems I dont completely get your system yet. I have only done a few inventory files, and they dealt with 100-1000 products. I imagine I would consider the problem differently if I had to contend with 20,400+ products. You poor soul...

Also- what are all relevant stock quantities kept in the line items file, or is the line items a record of actions that modify a base # in products?

And-if you

can see 150 products "Via della Seta Oro 30x30 4fgk5" in my stock and then I must select each shade/calibre to check the remaining quantities per shade.

Where does it take you to see this remaining quantity per shade number? LineItems?

Try Posting the attachment compressed on this thread- third times a charm.

[Ugo DI LUCA]

OK, I'll try to send the new attachment this evening (+ 6 hours from NY), when I come back home. But here are answers to your questions :

Question 1 :

These 150 products can be seen in a field called "RealStock_Product_ID" in the Product file which is a simple calc based on Relation 1 (Product:Product_ID::LineIteltemProduct_ID)

RealStock_Product_ID = QtyStock_Product_ID - QtyReserved_Product_ID

where :

QtyStock_Product_ID = SUM (Relation1::Qty P.O.) - SUM (Relation1::Qty Invoice)

QtyReserved_Product_ID = SUM (Relation1::Qty Remaining in CustOrder)

with Qty Remaining in CustOrder = IF(Customer_ID#"Myself"; Case(Qty invoice >0;0;Qty Customer Order);0)

Question N

[Ugo DI LUCA]

Here is the new attachment.

[Ugo DI LUCA]

I finally did it . Yeah.

Serial number. That is the way to go...

I was remodeling the definitive line items with serial numbers for Customer orders and Purchase orders, in order to have a field with 1-1,1-2,1-3 where the first digit stands for the Customer Order and the second the line number for this Customer Order.

Suddenly, all became clear even for that case.

Using a selfjoin based on the concanated field Product_IDandshadecalibre = Product_ID&Shadecalibre.

1. I created a autoentered Serial_per_ProductandShade

2. I created a c_NextSerialN