Hi everyone,
Lets get straight to it
I would like to sum the quantities of added products to an invoice of the same ID in the portal
To make it more clear, so it would be like Sum( Invoice::Product_Qty ) .. This way it will sum all products with different IDs but my goal is to sum the product quantities based on their unique product IDs
Maybe i should use ExecuteSql? If it is the only way then how?
At the end thank you so much for any help you provide, i do really appreciate the little
ExecuteSQL, could be used for that, but it's not the only option. How will you display these total quantities?
Assuming the standard Invoices----<LineItems relationship, a self join to a second occurrence of LineItems could yield that total:
LineItems::ProductID = LineItems|SameProductSameInvoice::ProductID AND
LineItems::InvoiceID = LineItems|SameProductSameInvoice::InvoiceID
Then Sum ( LineItems|SameProductSameInvoice::Qty ) Set to evaluate from the context of LineItems will yield the requested sub total.
I don't know if this helps, but I will describe my method:
1. use a pick list to choose a product
2. validate against current item list of chosen products and (your choice):
a. add new line to portal as is (no sum)
b. prevent adding same product to portal, suggest changing the qty on the current line item
c. use the new qty for the same product and ONLY increment the qty for that same product in the line items.
3. these validations may require other rules (if you order 5, you get a discount of 3%, if you order 20, the first 5 are free, etc.) You may not have these kinds of needs, but I have some clients who do. I try to make even the less complex prepare for such possible future actions. So mostly I'm getting you to think a little more before deciding a course of action.
NOTE: this method means just entering items in a portal will not allow the tricks. You must have a "pick list" and scripting to follow-through.
Beverly
Actually, you can prevent selecting the same product twice even when selecting directly in the portal row as there is a validation calculation that can be set up to catch those. or the product value list can "dwindle" to drop out previously selected products.
Like beverly, I don't know whether you want to prevent picking the same product twice, but if you do, there are a number of methods that can prevent such duplicate selections.
A hidden text field with an auto-enter calculation that combines product and invoice IDs can be set to validate only on unique values. This will cause an error to pop up the second time that a user picks the same product on the same invoice.
A Dwindling value list--a special kind of conditional value list can update to drop out previously selected products.
A "selection portal" (which is what I think that beverly has in mind can update to drop out products as they are selected (Or they can dim and become "disabled", or they can offer to update the existing line item's Qty field for you.)
If your product list is fairly short, you can also just show a master list of all products with a Qty field where you enter the quantity. Entering data into this field, automatically creates a line item record the first time that you do so and since every product is listed once, you can't get two line items with the same product on the same invoice. It takes a fair amount of typing to describe this method or the dwindling value list option, so post back and let us know if you want to know more about those options.
Wow it is like you knew what i was doing without me explaining what i have behind the scene
My test results (For your info):
What i'm trying to do is that i would like to check if added quantity is greater than the available quantity,
so if the user has added product twice in the portal then i would like to count the added countity in both rows,
For example, a product with Qty = 20, so if the user added the product two times, the first time 15 and the second time 6, then a message would appear to say " Hey buddy you are adding more than the available quantity!"
What i have done is, i have used the Sum function Sum(Invoice_Lineitems::Product_Qty) and it only works in the same portal row, so for the example of Qty = 20, if i add Qty = 21 in the same row then the message would appear, but it won't if they are two rows with the same product and the user can add more than the available quantity! philmodjunk your way in the first reply gave the same result!
The best to do (Correct way):
beverly 
and philmodjunk, your suggested way of preventing adding product duplication is the best way of doing it which i have never thought of actually!
Thank you so much for your help!
It's not polite to flame on this forum. Including yourself! The correct term is ignorance. There are no stupid questions (lack of intelligence), only ignorant ones (lacking knowledge). We are all here to learn. Now that you know, tag, you're 'it'. Pass it on to the next person who asks the same thing.
Welcome to the community, BTW!
Beverly
Thanks!
philmodjunk continues in the following topic,
This is for anyone reaches this topic and looking for an answer on how to prevent duplicate values in a portal
In the context of what you are trying to do, there are better options, such as a dwindling value list or selection portal. To name two of several options.
Thanks philmodjunk,
I'm actually using a selection portal, and i have used the script you provided in this topic to prevent adding the product when it already exists in the portal of LineItems, and it works well as wanted!
It would generally be better to drop items out of the selection portal as they are selected to keep that item from being selected a second time.
It’s better to prevent an error than to detect and handle an error after it has happened.
Thank you so much for making the idea clearer!
May you please explain the way of dropping items from the selection portal after they have been added to the invoice?
Relationship based conditional value lists and selection portals both start from the same thing: A relationship that controls what values appear in the portal or appear in the value list. Thus, many tricks that work (at the relationship level) can be used in either.
See the relationship used in this Dwindling Value List example. It can be used as the relationship for a selection portal (where it can then be further filtered, sorted, etc if need be.)
Adventures in FileMaking #1--conditional value lists
I'm referring to the relationship based on listing all values not listed in an "exclusion list" which happens to list all previously selected values.
Actually, you can prevent selecting the same product twice even when selecting directly in the portal row as there is a validation calculation that can be set up to catch those. or the product value list can "dwindle" to drop out previously selected products.
Like beverly, I don't know whether you want to prevent picking the same product twice, but if you do, there are a number of methods that can prevent such duplicate selections.
A hidden text field with an auto-enter calculation that combines product and invoice IDs can be set to validate only on unique values. This will cause an error to pop up the second time that a user picks the same product on the same invoice.
A Dwindling value list--a special kind of conditional value list can update to drop out previously selected products.
A "selection portal" (which is what I think that beverly has in mind can update to drop out products as they are selected (Or they can dim and become "disabled", or they can offer to update the existing line item's Qty field for you.)
If your product list is fairly short, you can also just show a master list of all products with a Qty field where you enter the quantity. Entering data into this field, automatically creates a line item record the first time that you do so and since every product is listed once, you can't get two line items with the same product on the same invoice. It takes a fair amount of typing to describe this method or the dwindling value list option, so post back and let us know if you want to know more about those options.