Ugly calculation help and/or restructure?

[LaRetta]

Hi everyone,

I keep getting the feeling that my logic is wrong on this one - or that my calculation result just plain sucks. Every time I look at the structure of it and the resultant calc, I shudder. And if I shudder, I figure it's pretty wonky.

I have a Rules table with one record. It contains several fields which mostly hold numbers. These fields determine when various actions should happen. Some actions are event-triggered; some display on a Sales Reps desktop requiring action; some are used in calculations and so forth. And Management (in Browse) can manipulate these rules to tweak their own business according to their needs. They can specify when an Invoice is faxed as past due; when a Contact should be called after a sale and so on. There are currently 53 rules.

I attached Rules to Contacts (on Cartesian) because it seems that everything applies to Contacts and I figured I could then draw the Rules through to other tables as needed without having to create addition Rules TOs. It seems to work well but I question the efficiency of it and the resultant calculations. Example:

The Invoices table uses these Rules as well. Invoices is joined to Contacts on ContactID. I have a calculation in Invoices which computes the due date of various invoices and it is God-awful ugly.

PIF is a paid-in-full flag, standard number (1 paid, 0 balance owing) which is event-triggered. This eliminates finding on summarized LineItems on an unstored calc and it can be indexed for joins. ShippedFlag is standard number (1 shipped, 0 unshipped) scripted when an order is shipped. Invoices, paid in full when the invoice is printed, shouldn't show a due date.

So here's the (unstored) date calc (cDueDate) from the context of Invoices. Can I get input on the calc - and the overall theory I'm using to pass these Rules to a second level table? I'll restructure if it'll improve my system. Thanks!!

If(

not PIF and ShippedFlag;

Case(

PayMethod = "COD"; PhysicalShipDate + Rules::InvDueCOD;

PayMethod = "Credit Card"; PhysicalShipDate + Rules::InvDueCC;

PayMethod = "Cash/MO/Cert Chk"; PhysicalShipDate + Rules::InvDueCash;

Contacts::Terms = "Net 30"; PhysicalShipDate + Rules::InvDueNet30;

Contacts::Terms = "Net 15"; PhysicalShipDate + Rules::InvDueNet15

)

)

Reality check anyone? I'd really appreciate it.

LaRetta

[Ender]

Can you explain more about the structure of the Rules table? Is there only one record, or is there a record for each Contact?

[LaRetta]

Hi Mike,

There is only one Record in the Rules table. Rules apply to all Contacts equally, depending upon the Rule. Sorry for being unclear. I should also mention that this is a networked solution and the Owner will be changing the rules from his system. So I wanted to avoid globals for holding the Rule data. If you require anything else, let me know.

LaRetta

[Ender]

It seems to make sense. You may wish to cover the cases where a field is left blank.

The only other thing I can think of is that you may wish to store the results. This would improve search speeds a bit and ensure past invoice due dates have not changed because the rules have changed. I think this could be done with an Auto-Entered calc, though it may need a refresh capabilty if PayMethod changes or something.

[LaRetta]

Hi Mike!

"The only other thing I can think of is that you may wish to store the results."

Bingo! Maybe this is part of what was bothering me. I've worked to keep it lean and then plopped an unstored calc on the end. No wonder this process made me shudder.

"This would improve search speeds a bit and ensure past invoice due dates have not changed because the rules have changed."

I thought I was safe because invoices are posted and frozen when printed and shipped so their PayMethod won't change (in this example) BUT ... if Owner changes the Rules, it would change this DueDate and, as you've indicated, will make searching longer!! And once an Invoice is printed/shipped/posted, I don't WANT the DueDate to change! Good grief!! I will change this DueDate to standard date field and script set it (with the calc) during the ship process.

My other uncertainly revolved around efficiency between passing data from Rules to Contacts to Invoices (and possibly beyond) instead of creating a second TO of Rules and attaching directly to Invoices (or in every place needed). My mind-set keeps thinking a direct join would be quicker. Is that 'old version' thinking?

Much appreciated, sir.

LaRetta

[Ender]

From what I've seen so far, there is no performance degradation from accessing a table that is many tables away vs. accessing a table that is directly connected.

[Søren Dyhr]

And once an Invoice is printed/shipped/posted, I don't WANT the DueDate to change! Good grief!! I will change this DueDate to standard date field and script set it (with the calc) during the ship process.

Could still be autoentered though, by setting a flag in the calc' to prevent "replaces existing values" to work ...I have a feeling that a stressed salesperson might circumvent a specially designed print script accidentally, and use the shortcut he/she uses all over the place. The safest bet would probably to make DueDate oneshot, and if something wrong has been entered - must a script lift the flag.

http://www.filemakerpros.com/LOKFIELD.sit

http://www.filemakerpros.com/LOKFIELD.zip

--sd

[LaRetta]

Hi Mike

Thanks for putting my mind at ease about flowing through several levels. There is a devilish (tenacious) part of me that will still try to run a few efficiency tests when I have time. If direct-connected vs. six levels deep (for example) might save even a fraction of a nanosecond, I'll surely do it. Speed is an obsession of mine.

Hi Soren, I have procedures in place to protect from my Users - they circumvent nothing in my solution. It's from myself that protections need to be established!! The DueDate is computed at the time of shipment - not invoice creation (our Sales Orders ARE our Invoices), nor at the scheduled ship date. Scripting the DueDate upon shipment, posting and lockdown is simply the easiest and I have rock-solid lockdown, posting and backout proceedures in place already.

LaRetta

[-Queue-]

I would only simplify it by pulling the PhysicalShipDate out of the Case.

If(

not PIF and ShippedFlag; PhysicalShipDate +

Case(

PayMethod = "COD"; Rules::InvDueCOD;

PayMethod = "Credit Card"; Rules::InvDueCC;

PayMethod = "Cash/MO/Cert Chk"; Rules::InvDueCash;

Contacts::Terms = "Net 30"; Rules::InvDueNet30;

Contacts::Terms = "Net 15"; Rules::InvDueNet15

)

)

You could also substitute GetField("Rules::InvDue" & Case( PayMethod = "COD"; "COD"; PayMethod = "Credit Card"; "CC"; ... ) ), in place of the Case, but that might make it a little more confusing.

[Ugo DI LUCA]

Hi LaRetta,

I have no idea how this could be easier or even if it might work, but it seems as each of your cases lead to a given relationship, which is either valid or not.

So I was thinking of something like...

If(

not PIF and ShippedFlag; PhysicalShipDate +

Lookup(Rules::InvDueCOD;Lookup(Rules::InvDueCC; Lookup(Rules::InvDueCash; Lookup(Rules::InvDueNet30; Lookup(Rules::InvDueNet15;0))

[Ugo DI LUCA]

Ok, I misread your post about the one record in your rules Table.

Though having one record per rule would seem IMO more appropriate, wouldn't it ?

Here's attached the solution which in fact turns out to be an indexed calc of

If(

not PIF and ShippedFlag; PhysicalShipDate +

Lookup(Rules::daysPlus;0))

RulesAndInvoices.zip

[Ugo DI LUCA]

Going back to the One Record Rule Table, here's a revisited method involving the nested Lookup functions in an indexed calculation.

I found this lookup( ) really interresting in fact for just this indexing purpose, although an auto-enter with re-evaluation might work as fast and efficiently.

RulesAndInvoiceOneRecord.zip

[LaRetta]

Sorry to take so long to get back to you two on this.

JT, I put your calc to work immediately and thank you for fine-tuning it for me. I always appreciate having my calcs tweaked.

Ugo, hmmm, as usual you leave me a bit befuddled. The one-record theory for a Preference-style table (to replace globals) is pretty common; although I'm always open to alternate methods.

This new Lookup() function has intrigued me for quite some time and it's on my 'must devour' list. I haven't had a chance to play with your demo files but you can be assured I will and thanks for bringing my attention back to the new Lookup() function.

For now, other projects are pressing in and I won't have a chance to reconsider this issue for another month or so. I believe there are many possibilities for this new function and I'm excited about discovering them.

Thanks everyone for helping me.

LaRetta