Summary from related records in a portal

[Gillekes]

Hello everybody,

I'm struggling with summary fields in a related portal. My situation is as followed:

I have an Article table, a Packaging_join table, a Packaging table, a Stock_Entry table and a Stock_Join table.

Relation: Stock_entry -< Stock_Join_Article >- Article -< Article_Join_Packaging >- Packaging

This works very well, I can enter different quantities per package per article.

I made summary fields in the Stock_Join_Article table to see how many article I have for each type of packaging. When I create found sets for each package I get the correct amount in stock, so this seems to works.

Now I would like to create a portal on the article layout which shows the quantity for each type of packaging for that article.

I have a hard time in crating this relationship. I can get it to work for the first line of the portal - the sum is correct. But for the following lines with the other packaging, the portal shows the sum for the first line which is not correct.

Could someone help me out how to create this relation ship?

 

Thank you for your time.

Gilles.

Edited November 27, 2016 by Lee Smith
Changed font style from the default of General Topic

[comment]

Define a relationship between Packaging and a new occurrence of the Article_Join_Packaging table as:

Packaging::PackagingID = Article_Join_Packaging 2::PackagingID
AND
Packaging::gArticleID = Article_Join_Packaging 2::ArticleID

where Packaging::gArticleID is a global field.

Define your portal to show records from Packaging, and place the summary field/s from Article_Join_Packaging 2 inside the portal.

Use a script triggered OnRecordLoad to populate the global gArticleID field with the ID of the currently viewed  record in the Article table.

---
 

Edited November 27, 2016 by Lee Smith
I removed that formatting

[Gillekes]

Hi Lee,

 

Thank you so much for answering. Sorry for posting in the  wrong place in the forum.

The summary fields are in Stock_Join_Article, not in Article_Join_Packaging. Can I use the Stock_Join_Article table in the portal with this relationship?

Also how should I populate the gArticleID? Should I make a var of the ArticleId and use the go to Packaging layout and thens use the set field? This seems not to be working as there are severela packagings and whisch on should I use?

Thanks Gilles.

 

[comment]

I am afraid at this point I am not sure what you want to accomplish here. What fields do you have in the Article_Join_Packaging table?

[Gillekes]

Ok,

Article Join is a join table between different kind of packaging (1kg,2kg,3kg,etc.) and the aricles.

Fields in the Article_Join_Packaging:

ID, ArticleIdFK and PackagingIdFk.

The Stock_Join_Article is a join table between stock entry and the article. (ie  1st line: 3 article1 of 3kg, 2nd line: 10 article 2 of 1kg, 3rd line: 5 article2 of 10kg, etc...)

So the sum of quantity available for each article per package is done in the Stock_Join_Article.

 

Edited November 28, 2016 by Gillekes

[Gillekes]

Hi everybody,

 

Don't know if it is clearer now what I want to achieve?

Thanks.

 

Gilles

[comment]

On 11/29/2016 at 6:44 PM, Gillekes said:

Don't know if it is clearer now what I want to achieve?

Not really. We need a worked out example here. Suppose you have these records in the Article_Join_Packaging table:

1.   Article 101     2kg
2.   Article 101     3kg
3.   Article 101    10kg
4.   Article 101     2kg
5.   Article 101    10kg
6.   Article 101     2kg

Then the summary for Article 101 might look something like this:

3 x  2kg
1 x  3kg
2 x 10kg

The records in the other two tables are rather irrelevant here.