Jump to content
Sign in to follow this  
angelleye

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

Recommended Posts

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 by Guest

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by Guest
typo

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.