If I had a dollar for everytime this issue pops up in the forum....
FilterValues will only return the value "open" or Null, it won't affect what records in the related table are summed. An If or case function can be used here to control whether or not any records are summed, but cannot be used to make the sum function selectively sum records from the related table.
If you use sum, the relationship used, must fliter out the values you do not want summed. Either that or you use a completely different method.
If you defined a calculation field, constOpen as just the quoted text: "Open", you could define this relationship:
Company::CompanyID = OpenOpportunity::CompanyID AND
Company::constOpen = OpenOpportunity::status
Then Sum ( OpenOpportunity::FuturePotential ) will return the desired total. OpenOpportunity is a new occurrence of your existing Opportunity table.
If you define a summary field, sTotalFuturePotential as the total of the FuturePotential field in the opportunity table, you can put a one row portal to Opportunity on your layout and place sTotalFuturePotential in that portal row. You then include this portal filter to get only Open status records included in the total: Opportunity::Status = "Open".
Option #2 works like a charm!
I wouldn't have come up with that solution in a 100 years. You never cease to amaze. :)
More calculations are on the way :(
I have a same issue like this topic.
And maybe you can help me too Phil, as I know you hv done help a lot of people who posted in this forum.
I hd created a table called DATABASE 1 QTY
which appear all my data such as RECEIVING, TRANSFER, POS, INVENTORY STOCKS, etc.
I hv only one DATABASE 1 QTY::Qty field, that record all quantity in every transactions process.
and wanted to control it to appear in other several tables, such as ITEMS and STORES, per ITEMS, per STORES.
For info, I hv 1 table Store called "MAIN STORE" and 1 other table Store called "ANAK MAS". that 2 table will contain information of my stores.
I also have ITEMS table called ITEMS LIST, which contain every details of my Items. In every items, I put 2 field (ITEMS LIST::Unit on MAIN STORE) and (ITEMS LIST::Unit on ANAK MAS), bcoz I would like to appear the Qty of the selected Item in each store.
Here are some calculation that Im willing to do but tottally doesnt work : (for example in Units on ANAK MAS field)
Case (DATABASE 1 | QTY::Store Name = "ANAK MAS" ; Sum (DATABASE 1 | QTY::Qty) ) - Case (DATABASE 1 | QTY::From Store = "ANAK MAS" ; Sum (DATABASE 1 | QTY::Qty) )Note :Store name field will define stocks comes inFrom Store field will define stocks comes out.And my layout in DATABASE 1 QTY will looks like:Date --- Transaction Type ---Item Name--- Qty --- From --- To21/8/13--PURCHASED --- Necklace A --- 5 --- (BLANK) --- MAIN STORE22/8/13--TRANSFER --- Necklace A --- 5 --- MAIN STORE --- ANAK MAS23/8/13--POS --- Necklace A ---3--- ANAK MAS --- (BLANK)I wanted to have a calculation in my Necklace A record in ITEMS LIST table, that show how many stock left on what stores.It should be appear :Units on MAIN STORE : 0Units on ANAK MAS : 2Can you please help me with this?Thank you so much before!
Ok, I would like to pile on here and see if what I'm trying to do is possible.
I have 3 tables
Table 1 is related by ID and then by a date to match with table 2
Table 3 is related to table 2 by ID
Currently there is a field in table 1 that calculates the number of hours from table 3 through table 2. This works great and it's calculating perfectly.
However, I have an issue, there are some things being calculated that need to be filtered out. In Table 2 there is a field that is checked off if they shouldn't get credit for the item. If it's not checked, it should be null. I have added a field to Table 1, should null for each record. So the fields from Table 1 and table 2 should match when they are both null. But when I add the relationship criteria so that the relationship between table 1 and 2 now have ID, Date, Credit the Field in Table 1 that calculates no longer has any values.
I have checked the fields the records so that some are equal, some aren't but it doesn't seem to be calculating any of them. Any ideas for things I need to check?
Last 2 posters, please start a new post. How is anyone suppose to follow answers or provide help to 3 different posts? Too confusing.
@PhilModJunk: A summary field in a portal…: wonderful ! I didn't know this solution to get statistics from a filtered portal ! Thanks so much !