Calculation with related field

[ABC]

I am creating an order entry system. The items in the system MAY have a predetermined price, some may not and require the user to enter a price. Orders are entered through a layout using fields from a 'TunnelInvoices' table, and the items are recorded in a related table 'InvoiceLines'. I have the price field in the 'InvoiceLines' table set to auto-enter the related item prices from an 'Items' table (I don't use a calculation type so that the user can change the final value if there isn't one). There is one case where, if the ItemID = 100, the value is actually pulled from a field in the current 'TunnelInvoices' table. As a result, my auto-enter calculation is:

If ( ItemID=100 ; TunnelInvoices::CarBalance ; items::ItemPrice )

The value of 'TunnelInvoices::CarBalance' is set before any invoice lines can be created, so there is indeed a value there.

The problem: this calcuation works just fine for any entry EXCEPT when the ItemID=100. In that case, it works SOMETIMES but not all the time. Sometimes I have to re-select the ItemID number several times before the value is calculated. I cannot for the life of me figure out what that limiting variable or issue is that would prohibit this from calculated sometimes, but not others. Anyone kind enough to take a stab at this? I can send the file if desired.

Thanks in advance for any assistance.

marK

[Wim Decorte]

The key is in when the calculation triggers. In other words, when is the record committed. The calculation itself is extremely logical: if itemID is not 100 it won't use CarBalance. You need to look at the process of creating that record and see when in that process records are created.

You may want to abandon the auto-enter and go with a completely scripted process instead so you have full control over the process....

[ABC]

Yea, I've thought along those lines, but it doesn't add up logically that if ID<>100, the calculation works just fine. In other words, why would the record commit differently based on the value the user selects for ItemID? Any other ItemID value calculates immediately and just fine. Any other thoughts along those lines?

Thanks for your reply.

[SlimJim]

The problem arises when the If should evaluate as TRUE. So perhaps look at what may be happening then.

What does If(..)return if the field ItemID is empty?

Have you unchecked the Do not Evaluate ... Empty checkbox?

What happens if you turn the logic around and auto-enter

If ( ItemID<>100 ; items::ItemPrice ; TunnelInvoices::CarBalance )

do you still get intermittent problems with ItemID = 100?

[ABC]

Some good ideas...I did swap the logic, but the results are the same. Re: ItemID, it is the selection of ItemID that creates the InvoiceLines record, so it really isn't possible to NOT have an ItemID.

The ItemID is selected from a pull down menu populated from a list generated by all the ItemID's in the Items table. I can randomly select ItemID's on an InvoiceLine and the price changes properly with each selection. But as soon as I select ItemID 100, the price doesn't register. Again, clicking off the record and back into it, or selecting a different ID and then selecting 100 on the next line will finally 'engage' the calculation and get the value of CarBalance to be entered.

Any other ideas or help out there? Seems so stupidly simple...for all I've constructed that this is a problem I would have. I'm sure there's an obvious solution but I sure can't figure it out.

marK

[ABC]

OK. I think I might have solved my issue. Not sure why/how, but dare we ask sometimes? By changing the field type of TunnelInvoices::CarBalance to a global number, my calculation seems to always work. Since TunnelInvoices::CarBalance is revalued with every entry and is not used anywhere else, having it defined as a global makes no difference to the program. Have no idea why this would change the performance of a calculation, but I can't get it to fail now at all. Works perfectly. Grrrr. Sometimes these little things can be so annoying, yea? Would love anyone to offer an explanation at this point. Otherwise, thanks for the replies.

marK

[LaRetta]

Hi marK,

I've finally had a moment to actually read this thread. The break appears logical to me.

why would the record commit differently based on the value the user selects for ItemID?

You said it yourself ...

There is one case where, if the ItemID = 100, the value is actually pulled from a field in the current 'TunnelInvoices' table.

When you have something break and it only happens when an exception is applied, suspect the process first rather than the math. You can test the math by creating an unstored calc. If it works, it works. If it doesn't, it doesn't. If the math is right then it's the process.

The value of 'TunnelInvoices::CarBalance' is set before any invoice lines can be created, so there is indeed a value there.

There may indeed be a value but it isn't accessed unless ItemID = 100. And it can't be accessed because that tunnel record hasn't been committed yet. As Wim mentioned, you need a Commit Record/Request AFTER you set the CarBalance so the value is available when called with ItemID = 100. It works when the value is changed to global simply because globals are user-specific and do not suffer from commit issues.

Not sure why/how, but dare we ask sometimes?

Yes. Always question. Simply always. Because that is the only way to keep it from happening again. I've spent 100 hours figuring why something breaks to find out it's only a clicked 'Do Not Evaluate' box. My 'calculation' was correct but the process changed depending upon whether a field referenced was empty or not. That error will never happen again (because now I understand the difference it can make) and not ONE time do I open a calc box without considering how that checkbox will effect the outcome of a correct calculation.

What's the phrase? 'One who doesn't learn from the past is doomed to repeat it?'

Just some thoughts!

LaRetta

[SlimJim]

Since a solution has been found an explanation may be academic but it may also be useful in similar situations. Since it turns out to be the field TunnelInvoices::CarBalance at the root of the problem; making it global removes the necessity to commit the current TunnelInvoices record and hence the current value of CarBalance. Further evidence is that you can get the auto-enter calculation to work by clicking off the record (i.e. committing the tunnelInvoices record). So Wim Decorte was pushing the right direction and this is to do with committment.

[ABC]

Ah yes, brilliant. Brilliant, brilliant, brilliant. You are wise sages indeed. And of course, makes so much sense now. Never suspected something like that because in my layout, I actually have the value of CarBalance displayed on the screen so I didn't think to suspect that the TunnelInvoice record wasn't committed. So bullseye. Man, the little things. Cruising along all day again after getting by that one. Thanks all for the responses, I'll double my effort to try to repay the favor to this forum now.