Sum does not work like you have set up in this script.
Sum can be used three ways:
Sum ( Field1 ; Field2 ; field3 ) ---> This sums these three fields from the same record
Sum ( Repeating field ) ----> This sums the different repetitions of this field
Sum ( relatedTable::Field ) ---->sums field over all related records
Note that none of these sum across the found set.
Sum ( Field ) just returns the value of "field".
Use a summary field instead of Sum
And there are a number of ways to get these totals without using a script
I am a little confused how I can make a summary field based on a portal. I made an attempt by self-relating and using 'total', and it came out as not the right numbers.
I'm not suggesting a summary field based on a portal.
Simply define the summary field in Credits to compute the total of Rebate Amount.
Change line 8 of your script to be:
Set Field [$IsManufac ; Value: New Summary field here ]
This assumes that you want to put a value into this variable that is the sum of Rebate Amount across all the records of your found set.
Given that "Is manufacture rebate" with its yes / no value governs your table, it's legit to define
RebateYesAmount = If (Is Manufacture Rebate; RebateAmount; 0)
RebateNoAmount = If (Is Manufacture Rebate; 0; RebateAmount)
and define summary sums on those 2.
Then you can do your finds and directly evaluate the values of the sums.
siplus, that sounds like a very simple resolution that I didn't think about. But it does make some other things more complicated (work wise) going with that system.
Philmodjunk, I will give this a try (I am heavily multitasking currently, so hopefully i can get it tried soon.)
In the initial test, the script worked perfectly, HOWEVER, it does not. It only works great if there is at least 1 item in the non-manufacture rebate section, then totals will be zero'd out. I then made changes to the script to hopefully solve it. First, set the variables to 0 from the start, then added an if statement to only change the variable if they FoundCount is greater than 0 (stays 0 if there was nothing found), and reset the summary field to 0 after each count; but this had a weird effect of switching the spots of the variables.
Did I do something wrong, or did I just misplace something? (It worked 100% on a piece that had both manufacture and non-manufacture rebates; but excluding 1 from the current version has odd displays).
(I don't know if you want to poke at the file. But might as well give the current version up)
(This script is in the Bid layout, Complete tab, only button on the screen)
Bidding.fmp12.zip 1.9 MB
I can only assume, that for some reason it is finding records when it should not be finding records, so the summary field fills up a FoundCount is greater than 0. If there i nothing, it still subtracks some amount.
On the if statements, I changed them to check if lasterror is 401 (Which is/should be no records found_Or the error code doesnt exist). But even when the rebates are empty and error message pops up; it still ends up coming out at a subtracted number.....why. (I can't activiely view the data, because I don't have Filemaker Advance's Data Viewer.)
"Odd displays" is too vague to tell us anything useful.
Try putting a pause or show custom dialog in just after the find is performed and check to see what records are being found. Halt the script immediately after the find--you can insert a Halt script for temporary testing purposes and then you can not only examine the found set to see if it is correct, but you can select "modify last find" from the records menu and check the actual find criteria used to see if that is correct. (you might have to place additional fields, or fields with value list formatting removed on your layout to see the criteria--which can also be checked in the data viewer if you have FileMaker Advanced.)
I tried that, but it always shows as 0's, including "Total Round 5"(and 6); or as blank spots. Even lasterror is just '0' (placed right after performfind)
idiot me didnt think about checking which if statement portion it went to; and even though none are found with error capture off- the custom dialog for the section for if records are found is used instead.
if last error is 0 records were found.
Not sure what "that" you tried.
You are aware that getting these totals can be gotten without using a scripted find at all?
Relationships, filtered portal and ExecuteSQL can all provide you with aggregate values based on selected sets of records. That's why I posted a link to a very similar discussion that has been paralleling this one with a different person.
SQL is something I have no experience in, so I am confused just reading it. I know what it is, but without experience, is not something I can currently handle so simply.
Adding yet another reason to turn this project over to a competent developer.
There is so much about this project that could be done faster, and with proper structure.