Calculation seemingly not working correctly...?? Confused...

[angelleye]

I know I'm doing SOMETHING wrong, but I can't figure it out. The ultimate goal here is to mark an item Sold or Returned automatically based on it being sold or returned, obviously.

We have a tabled called Scans that stores all of our item's data with a unique ScanID. The Scans table is related to InvoiceItems on ScanID. This is a 1 to many relationship because a ScanID could end up with multiple InvoiceItems records.

I've created a calc. field in InvoiceItems called zInvoiceTypeCalc. The calc. for this field is as follows:

Case (

Invoices::Record Type = "Invoice" ; 1 ;

Invoices::Record Type = "Credit Memo" ; -1

)

So if the invoice record was created as a sale it sets this field in InvoiceItems to a 1. If it was a Credit Memo (return) it sets to a -1. I add this field to my InvoiceItems list layout and I see that it is working correctly. All items on a sale invoice have a 1 and all on return invoices have a -1.

So now back in the Scans item details layout I want to add a field that will automatically set to Sold or Returned based on the Sum() of InvoiceItems::zInvoiceTypeCalc. So I create a field in Scans called StatusAutoCalc with the following calc.

Case (

Sum( ScansToInvoiceItemsByScanID::zInvoiceTypeCalc ) > 0 ; "Sold" ;

Sum( ScansToInvoiceItemsByScanID::zInvoiceTypeCalc ) ≤ 0 ; "Returned" ;

Status

)

So if an item is added to an invoice for sale the zInvoiceTypeCalc field would be a 1 and this StatusAutoCalc should be Sold. If the item got returned there would be a -1 also so the Sum() would be 0 and the StatusAutoCalc should change to Returned. This is not happening.

I'm looking at one example now that has 2 InvoiceItems records. One was a sale and one was a return so there is 1 and -1 respectively. The Sum() should be 0...right..??

Well, back in Scans I added the InvoiceItems::zInvoiceTypeCalc field onto the layout thinking I would see both a 1 and a -1 in there since there are actually 2 records related. I do not, though, I only get a 1. My StatusAutoCalc field says Sold as well, instead of Returned like it should.

I added a GTRR button setup to the InvoiceItems::zInvoiceTypeCalc field on the Scans layout and when I click that I do indeed see both InvoiceItems records with 1 and -1.

Is my logic incorrect? What am I doing wrong? Any information would be greatly appreciated. Thanks!

Edited May 9, 2007 by Guest

[angelleye]

For a test I created a script with nothing but a Show Custom Diag step that displays Sum(InvoiceItems::zInvoiceTypeCalc) and it does indeed show 0 for this record I'm on, but the calc. that should be setting to Returned when that = 0 isn't working.

[comment]

The field in Scans must be an unstored calculation field. Auto-enters will NOT update when related records are added/modified.

BTW, the calc could be simplified to:

Choose ( 1 + Sign ( Sum ( ScansToInvoiceItemsByScan ID::zInvoiceTypeCalc ) ) ;

Status ;

"Returned" ;

"Sold" )

Edited May 9, 2007 by Guest
typo

[David Jondreau]

For a test I created a script with nothing but a Show Custom Diag step that displays Sum(InvoiceItems::zInvoiceTypeCalc)

The Data Viewer is your friend.

[angelleye]

Now I gotta throw even more chaos into the mix. I just found a flaw in the way I'm doing this because don't always create new Credit Memo invoices when they take a return. sometimes they just add that item with QTY -1 back onto the original invoice they created. As such, what I'm doing here checking the invoice type isn't going to work.

So then I figured, ok well if it is a return then it's gonna have a -1 for QTY. So why don't I just use Sum(Quantity) instead of using this new calc. I made in the first place..?? If an item is added to 1 invoice with qty 1 and another invoice (or credit memo) with qty -1 then I could take the Sum() and still get 0.

So I've made those changes and I added Sum(ScansToInvoiceItemsByScanID::Quantity) to my test script that displays its value and that does seem to be working as expected. However, I'm having the same problem with my calc. field not updating correctly. I changed the StatusAutoCalc to:

Case (

Sum ( ScansToInvoiceItemsByScanID::Quantity ) > 1 ; "Oversold" ;

Sum ( ScansToInvoiceItemsByScanID::Quantity ) = 1 ; "Sold" ;

Sum ( ScansToInvoiceItemsByScanID::Quantity ) = 0 ; "Returned" ;

Sum ( ScansToInvoiceItemsByScanID::Quantity ) < 0 ; "Duplicate Return"

)

I was thinking that might help us catch some problems that could occur if people put the wrong ScanID into an invoice or anything like that. The StatusAutoCalc field is not updating as expected, though. I'm looking at a record now where the Sum(invoiceitems::qty) returns 1 but it's setting the calc. to Oversold. Then there's another where the script shows that Sum(invoiceitems::qty) = 0 butt he calc. field shows Oversold for that one as well. Then I look at yet another where the script shows Sum(invoiceitems::qty) = 1 and it correctly displays Sold.

I'm as confused as ever and I'm sure I've done the same to you all, but anything you can tell me would be awesome. Thanks!

Edited May 9, 2007 by Guest

[angelleye]

You're right. I never knew about that. Thanks!

[angelleye]

yes, it is an unstored calc. field, not an auto-updating field.

[David Jondreau]

If you've followed comments suggestion (making the field an unstored calc field) and the problem is still happening, then there's either something going on with your relationship or you're misusing the Sum() function.

Post a sample file and you're more likely to get the right answer.

[angelleye]

Ok. that's gonna take me a little while cuz I'm on wireless and I have to transfer this whole collection of files so i can empty them out and reproduce what i'm doing here. they're not exactly small files so it's gonna take me a litle bit to transfer over wireless. i'll get it posted asap.

[angelleye]

Is there an easy/quick way to completely wipe out all data in all my files?

[comment]

Why don't you establish some check points, to see where the problem starts?

First, put a portal to InvoiceItems on a layout of Scans, and check that you are seeing all and only the records you expect to see. Next, make your calc just Sum(...) to see if the totals match your expectations.

[angelleye]

comment...actually i did something very similar. I added a go to related records button (didn't think about the portal until now) and when I click that I do indeed see only the related records that i expect to see.

I created a script to display the Sum(qty) of those related records (which I was just informed of a better way, data viewer) and this does return exactly what it should based on what the related records show.

but then the calc. is doing it's own thing somehow. again, i know i've gotte be doing something wrong but i simply can't find it.

i'll have a sample posted here soon...

[angelleye]

wow, i feel like such a rookie. i've opened up local copies of the files and i've gone into each layout and deleted all records. the files are still 60+ MB though..?? what gives? I looked in define database on one and it shows 1 table, 0 records. 65MB!?

Edited May 9, 2007 by Guest

[Ender]

Try Save a Copy As (Clone).

[angelleye]

Ok, that's taking way too ******* long trying to delete all this data out of our files. There's too many files and too many thousands of records in a number of diff. layouts in each. I'm gonna try something else instead...

http://www.dealexpress.com/temp/filemakercap/fmcap.htm

That should open up a Flash video for you of my FileMaker. I had audio included but it wouldn't stay in sync for some reason when I exported the video so I'll just have to talk you through it here but you can watch the video too.

When the video first starts it sits on the opening page for a min. This layout is my Scans layout and I added a portal to it showing the related InvoiceItems as suggested. Below the portal is the StatusAutoCalc field that I'm trying to get to update correctly and below that is the actual calculation that field is using (just there for reference.)

As you can see this very first record it's showing is incorrectly calculating to Oversold. You can see for yourself from the portal that is should Sum() to 1. The video opens up the Data Viewer to see this as well. However, the calc. field is displaying Oversold even though the calculation clearly says to change to Sold when Sum() = 1.

Then I switch to my InvoiceItems table. the video gets kinda choppy here but you quickly see the entire list before I click into anothoer one to look at.

Once you're in the next one you can see that it has a 2 for the Sum() and shoul indeed evaluate to Oversold...and it does.

Then I go back to invoice items again and try another. This one evaluates to 0 which should show Returned, but it still shows Oversold.

Then I try another which Sum() is 2 and it does show Oversold correctly.

then I try another that Sum() is 0 and it correctly displays Returned in the calc. field.

then another that evaluates to 1 and correctly shows Sold.

And then I go back and show you the first one again that evaluates to 1 but shows Oversold instead of Sold.

then another that evaluates to 1 and correctly shows Sold (might have been the same as the first Sold one, i can't remember.)

then another that correctly displays Returned because Sum() is 0.

then finally back to the first one again that shows Oversold even though Sum() is 1.

I'm gonna keep messing with an actual copy of the files to give you guys but I was hoping this might help in the mean-time.

[comment]

Why don't you make a new file, with just the three tables and the minimum fields? Then, if you can't make it work, post it here.