mac Intermittent problem with calculated value

[ronnytann]

I have a portal that shows the line items in an invoice. The field "unit price" which is a number field with a calculated value, is not consistently showing a value; sometimes it works, sometimes not. Once it works for a given portal line, the following lines for that portal, in that record, will work. The next new invoice record will often show the same erratic behavoir in the portal.  The calculation involves a join to a related table that contains the prices for the items that are entered in the portal. The join is clearly ok, since values often show up, but not reliably. I'm totally perplexed as to what is causing the intermittent behavoir of the calculated value.

[comment]

The calculation involves a join to a related table that contains the prices for the items that are entered in the portal.

 

That's a rather vague description. Still, in the absence of more tangible clues, I would guess you are seeing a refresh issue. You can test this by doing :

Refresh Window [ Flush cached join results ]

and see if that fixes the display.

 

 

Unrelated (?) to your question, but shouldn't you be looking up (i.e. copying) the price into the line item record? Price are bound to change from time to time, and your line items should preserve the price as it was at the time of issuing the invoice.

[ronnytann]

Fair enough it was vague, here is what is involved in the calculation in detail(the related table that seems to be the problem is "Price List"):

 "unitprice" = Case (/*LINERS CARRIZO*/Types::typeID=16 and Rootstocks::priceclass=.95 and quantity <1000;Price List::carrizo999; Types::typeID=16 and Rootstocks::priceclass=.95 and quantity >1000 and quantity < 2501 ;Price List::carrizo1000; Types::typeID=16 and Rootstocks::priceclass=.95 and quantity  > 2500 and quantity <10001;Price List::carrizo2501;Types::typeID=16 and Rootstocks::priceclass=.95 and quantity >10000;Price List::carrizo10000;/*LINERS TRIFOLIATE CLASS*/  Types::typeID=16 and Rootstocks::priceclass=1 and quantity < 1000 ;Price List::trifol999; Types::typeID=16 and Rootstocks::priceclass=1 and quantity  > 1000 and quantity <2501;Price List::trifol1000;Types::typeID=16 and Rootstocks::priceclass=1 and quantity >2500 and quantity<10001;Price List::trifol2501;Types::typeID=16 and Rootstocks::priceclass=1 and quantity >10000;Price List::trifol10000; /*FLYING DRAGON CLASS*/Types::typeID=16 and Rootstocks::priceclass=1.05 and quantity < 1000 ;Price List::mac999; Types::typeID=16 and Rootstocks::priceclass=1.05 and quantity  > 1000 and quantity < 2501;Price List::mac1000;Types::typeID=16 and Rootstocks::priceclass=1.05 and quantity >2500 and quantity<10001;Price List::mac2501;Types::typeID=16 and Rootstocks::priceclass=1.05 and quantity >10000;Price List::mac10000;/*BUDWOOD TO NSY, INCREASE BUDS*/Customers::customertype="Nsy" and Types::typeID=12 and quantity<500;Price List::bud499; Customers::customertype="Nsy" and Types::typeID=12 and quantity>499 and quantity<2001;Price List::bud501; Customers::customertype="Nsy" and Types::typeID=12 and quantity>2000 and quantity<5001;Price List::bud2001;Customers::customertype="Nsy" and Types::typeID=12 and quantity>5000;Price List::bud5000;/*BUDWOOD TO NONNURSERY,INCREASE BUDS*/Customers::customertype ≠ "Nsy" and Types::typeID=12 and quantity<500;Price List::bud499+Varieties::patentfee; Customers::customertype ≠ "Nsy" and Types::typeID=12 and quantity>499 and quantity<2001;Price List::bud501+Varieties::patentfee; Customers::customertype ≠ "Nsy" and Types::typeID=12 and quantity>2000 and quantity <5001;Price List::bud2001+Varieties::patentfee; Customers::customertype ≠ "Nsy" and Types::typeID=12 and quantity>5000;Price List::bud5000+Varieties::patentfee;/*TOPWORKING,REGISTERED BUDS TO NSY*/(Types::typeID=18 or Types::typeID=23) and Customers::customertype="Nsy" and quantity<500;Price List::ri499;(Types::typeID=18 or Types::typeID=23) and Customers::customertype="Nsy" and quantity>499 and quantity<2001;Price List::ri501;(Types::typeID=18 or Types::typeID=23) and Customers::customertype="Nsy" and quantity>2000 and quantity<5001;Price List::ri2001;(Types::typeID=18 or Types::typeID=23) and Customers::customertype="Nsy" and quantity>5000;Price List::ri5000;/*TOPWORKING, REGISTERED BUDS TO NONSY*/(Types::typeID=18 or Types::typeID=23) and Customers::customertype ≠ "Nsy" and quantity<500;Price List::ri499+Varieties::patentfee;(Types::typeID=18 or Types::typeID=23) and Customers::customertype ≠ "Nsy" and quantity>499 and quantity<2001;Price List::ri501+Varieties::patentfee;(Types::typeID=18 or Types::typeID=23) and Customers::customertype ≠ "Nsy" and quantity>2000 and quantity<5001;Price List::ri2001+Varieties::patentfee;(Types::typeID=18 or Types::typeID=23) and Customers::customertype ≠ "Nsy" and quantity>5000;Price List::ri5000+Varieties::patentfee;/*FT SATS ON TRIFOLIATE*/(Rootstocks::rootstockID=17 or Rootstocks::rootstockID=18 or Rootstocks::rootstockID=23) and Types::typeID=10 and Varieties::satsuma="Y";Price List::ftsat+Price List::trifoliate;/*FT SATS*/(Rootstocks::rootstockID ≠ 17 and Rootstocks::rootstockID ≠ 18 or Rootstocks::rootstockID ≠ 23) and Types::typeID=10 and Varieties::satsuma= "Y";Price List::ftsat;/*FT NSY ON TRIFOLIATE*/Types::typeID=10 and Customers::customertype="Nsy" and (Rootstocks::rootstockID=17 or Rootstocks::rootstockID=18 or Rootstocks::rootstockID=23) and Varieties::satsuma ≠ "Y" and Varieties::varietyID ≠ 190;Price List::ft+Price List::trifoliate; /*FT NONSY ON TRIFOLIATE*/Types::typeID=10 and Customers::customertype ≠ "Nsy" and (Rootstocks::rootstockID=17 or Rootstocks::rootstockID=18 or Rootstocks::rootstockID=23) and Varieties::satsuma ≠ "Y"and Varieties::varietyID ≠ 190;Price List::ft+Price List::trifoliate+Varieties::patentfee;/*FT NSY*/Types::typeID=10 and Customers::customertype="Nsy"  and (Rootstocks::rootstockID ≠ 17 or Rootstocks::rootstockID ≠ 18 or Rootstocks::rootstockID ≠ 23 ) and Varieties::satsuma ≠ "Y"and Varieties::varietyID ≠ 190;Price List::ft;/*FT NONSY*/Types::typeID=10 and Customers::customertype ≠ "Nsy" and (Rootstocks::rootstockID ≠ 17 or Rootstocks::rootstockID ≠ 18 or Rootstocks::rootstockID ≠ 23 ) and Varieties::satsuma ≠ "Y"and Varieties::varietyID ≠ 190;Price List::ft+Varieties::patentfee;/*FT M7 ON TRIFOLIATE*/Types::typeID=10  and (Rootstocks::rootstockID = 17 or Rootstocks::rootstockID = 18 or Rootstocks::rootstockID =  23 )and Varieties::varietyID = 190;12.5;/*FT M7*/Types::typeID=10  and (Rootstocks::rootstockID  ≠  17 or Rootstocks::rootstockID  ≠  18 or Rootstocks::rootstockID  ≠   23 )and Varieties::varietyID = 190;11.5;/*CUTTINGS*/Types::typeID=13 and quantity<1000;Price List::cut999;Types::typeID=13and quantity>999 and quantity<2501;Price List::cut1000;Types::typeID=13and quantity>2500 and quantity<10001;Price List::cut2501;Types::typeID=13and quantity>10000;Price List::cut10000)

 

So when it is working ,the quantity and the type are entered in the portal, and the "unitprice" value is displayed. When it doesn't work, the value shows as"$0.00", or if there is a value in "Varieties::patentfee", it is showing, but the rest of the formula which involves the "Price List"  table isn't coming into play. It is as if sometimes it can't "see" the "Price List" table.

 

In fact in further playing around I discovered that if a new record is opened, then I go to manage database, hit cancel, go into the portal that involves the calculation, it seems to work from there on in that record. The same procedure is needed on the next new record....

 

Per your suggestion, refreshing appears to have no effect.

[comment]

LOL, do you seriously expect anyone to read all that and point out a possible problem?

 

I do see however that you have several related tables referenced in there - and we know nothing about how they are related to your current record. It seems quite clear that some action is required to make these work; flushing the cached join results seems like the natural suspect, but it could also be as simple as committing the records (or a combination of the two).