Exactly what do you want to see for Quantity in each portal row?
The contents of the Quantity field in orders for a specific order? Or a total of the Qty field for all orders made for that share by a given customer?
In the layout CUSTOMER i need this:
SHARE X TOTAL SHARE
SHARE Y TOTAL SHARE
SHARE Z TOTAL SHARE
In the layout SHARE i need this:
CUSTOMER X TOTAL SHARE
CUSTOMER Y TOTAL SHARE
CUSTOMER Z TOTAL SHARE
At the moment i have got all the portals working
For the first portal shown in your last post, if Customer Smith orders Share X twice, do you see it listed once or twice in your portal? If you see it listed twice, is this a problem?
i see once. the problem is the sum of the quantity
in portal customer i need in every row of the portal where i find in each row a share the total of that share for that sutomer
but the sum is always strange like the sum of the share for ALL the customer or somthing like this
How do you get it to only list given shares once if a customer has placed multiple orders for it? Please describe how you have set up your portals in detail. Please confirm the following relationships:
Customer-----<Orders----<Shares (----< means 1 to many )
CUSTOMER::CUSTOMER_id = ORDERS::CUSTOMER_id
ORDERS::SHARE_id = SHARES::SHARE_id
YES THE RELATIONS FIELDS ARE OK
CUSTOMER SHARE QTA
JOHN MSFT 100
MARK IBM 1,000
LUIS ORCL 1,000
LUIS IBM 500
JOHN MSFT 100
JOHN ORCL 500
MARK F 200
MARK MSFT 100
So if i go into the customer layout FORM view
IN THE LAYOUT SHARE FORM view
LUIS 500 sorry
You have a portal on your customer layout to list shares purchased by that customer. Is it a portal to Orders or a portal to shares?
And on your shares layout you have a portal to customers?
And do you have FileMaker 11? (I think a filtered portal can be used here, but that requires Filemaker 11 to pull off.)
in the customer layout is a portal to shares
in the share layout is portal to customer
I am going mad.....
Hmmm, still thinking about this one. I can add a summary field to Orders and by adding a new occurrence of it to link to shares, I can get a portal that lists the total quantities, but I'm getting a row for each time the share is ordered instead of just one for each share...
Will this be a portal on a customers layout that is always viewed in form view?
yes is in a Form view.
I try also me to add a sum over there
but i get strange results, like for layout customer in the portal row af the first share the sum of all the quanty of that share and not for that customer
i mean if i have john 200 ibm and mark 500 ibm, in the layout jonh i have 700 and some times if john also bought another share like ibm for 1000 in that portal on the second row ( the ibm row) the result maybe the sum of 700 and 1000 or 500 and 1000 depends of how i change the summury in the portal
try everything but nothing at the moment
Ok, this relationship can produce the totals you want with a list of a single entry for each share purchased by that customer, but it uses a script to update a filtering global field to limit the share quantities totaled up to the current customer. Thus it will work with Form View, but not in a list view where you would look for these portals for more than one customer at a time...
In Manage | Database | relationships, make a new table occurrence of Orders by clicking it and then clicking the duplicate button (2 green plus signs). Double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as OrdersByShare_gCustomer.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
Shares::gCUSTOMER_id = OrdersByShare_gCustomer::CUSTOMER_id AND
Shares::SHARE_id = OrdersByShare_gCustomer::SHARE_id
Shares::gCUSTOMER_id is a new field that you add to your database and for which you specify global storage. (hences the g naming convention here)
Now add a calculation field, cTotalQty to Shares and define it as Sum ( OrdersByShare_gCustomer::QUANTITY )
And now you can add cTotalQty to your portal rows.
The final step is to set up the OnRecordLoad script trigger to perform this script:
Set Field [Shares::gCUSTOMER_id ; Customer::CUSTOMER_id ]
Refresh WIndow  //this step may not be needed
i have to create gCUSTOMER_id?
Yes and use field options to specify global storage for it.