Browse mode Contract Provisions

[LaRetta]

I had a bright idea. And now the Owner wants it. I've spent 6 hours cutting down the size and complexity of this post. Please don't chuck it aside just because it's long ... writing Provisions to an Agreement, that a business owner can use to dynamically control their business (ie, the Owner writing the calc so-to-speak) is POWERFUL stuff. Allow Owner to write the rules to a Staff Agreement (define the Provisions) and then script will carry them out and pay commissions accordingly (depending upon sales to Customer - staff is assigned to Customer). He would create Agreement record, select the Staff, specify a StartDate - end date open unless subsequent Agreement with same Staff (with later StartDate) was created. Owner would then create varying number of rules (as records) in a Provisions table by selecting from pre-defined 'sentences' in which he then plugged in rate, duration etc. The Provisions table would contain both StartDate and EndDate. If no EndDate then the Provision would be ongoing for duration of Agreement (some Provisions are probationary 3 months etc).

System will use this Staff Agreement to determine Commissions. Commissions table must be written static. Owner will then allow/disallow or change the results before checks are cut. Commissions table thus is necessary because I can’t have Owner change quantities in LineItems directly nor Invoice dollar sales. At month end, these Provisions (as dictated by Agreement) will:

1) Loop through each Invoice and write to Commissions based upon the Agreement in place on that Customer, looking at the Provisions to determine WHAT to write to Commissions.

2) Also loop through each LineItem and write to Commissions.

I want to provide Owner with Browse mode rule-writing. I want him to have set of Provisions that my script knows how to handle. Commissions can be paid on things such as:

1) Rate based upon Gross Invoice Sale.

2) Rate based upon whether it is a new customer sale (first sale).

3) New sku – first time this product has been sold to this customer … and so on

So Provisions will apply the rules to every invoice and every lineitem, writing to Commissions accordingly. I have it all in my mind and scripts currently know most of the rules and I just find the month’s invoices and LineItems and export these calculations to Excel. But we have no Agreements or Provisions tables yet – this is what I need help with. Main problem with existing process: Owner changes commissions in Excel and they never are changed in FM. Staff viewing their commissions are then confused in why it is different. But mostly it's because of complexity - script can handle rules better than Owner (working late at night in Excel) with 4,000 Invoices and 20,000 LineItems. Also very prone to User error.

I figure I can attach Agreements to Staff and draw the Provisions through to Invoices and LineItems when loop/writing. But how would I isolate the Provisions according to Invoice or LineItem Ship Date if Provisions is only attached to Staff? I *think* I’ll have to create more TOs to attach Provisions directly to Invoices and Lineitems as well where to attach - how to attach? I currently have NewSku table occurrence attached to LineItems (not in this example file) which tells me whether a product has been sold to customer before. And I have TO attached to Invoices which tells me whether the customer is new customer etc so I just need to transfer this scripting and logic to new structure which uses Agreements/Provisions instead.

One final twist … If the Owner has put a name in Promotion field in Products, that will daily write to any LineItems shipped (we post daily) until the promotion word is removed. That Promotion name is ALSO written to Commissions and Owner will pay additional bonus’ based upon that count. The Provision rules will not conflict (except if Product is promotion) but that will be built into it (via lookup from Product table daily) and in script logic when writing to Commissions. Another Provision may be ‘if you sell to over 10 new customers in a month you get bonus of (rate) per (qty). So should the Provision rules be fields?

ProvisionName: New Customer

Rule: If customer has not purchased within (duration), pay (rate) per (dollars) Gross Invoice.

Table: Invoices

ProvisionName: Standard Bottle

Rule: If not NewSku and not [color:green]Promotion, pay at (rate) per (each/qty).

Table: LineItems

So I need help creating the Provision table which allows Owner to plug in duration, rate, quantity and so forth. And I need help on how I would write that to Commission (again, as records, ie, one per LineItem and one per Invoice or as fields)? I started to attach 1:1 LineItems to Commissions and use fields (blush); same with Invoices. I only want to establish a set (template) of Provisions so my scripts can handle it. There will probably be 2 Invoice Provisions, 3 LineItem Provisions and 1 Customer Provision. I apologize for the length … I don’t know how else to give you my vision of it. I’ve attached a standard relational file which I hope assists if anyone wants to attempt to show me. Otherwise, just your thinking on how you would approach this ‘browse mode rules to script logic’ and ‘writing to Commissions fields/records’ would be helpful. The process and rules are very clear in my mind ... it's the proper relationship and records vs. fields that's twisting my head. So ANYTHING which will help me get clarity would be wonderfully appreciated. BTW, I've created several methods but they all are clunky so I didn't even want to cloud your thinking showing you what I've tried (including using repeating fields HA HA).

LaRetta :wink2:

Agreements.zip

Edited October 5, 2006 by Guest
Corrected green

[ThatOneGuy]

Good Morning, LaRetta:

I always enjoy reading your posts. You write in such a style that your compositions read more like a conversation, and it's refreshing. You also present interesting topics (see Lunar Phases v. Revenue), and your enthusiasm for FileMaker is often inspiring.... Okay, the Hallmark portion of this post is now concluded.

You raised numerous considerations, and since I'm still debating where to start, how 'bout I just jump to the end.

If ...

• the necessary commission profile fields were native to the Invoice table, and

• the "source" commission profile fields were present in a Staffperson's record, and

• that "source" profile was populated into the Invoice's commission profile fields at the time of Invoice creation through either (a) old-school Lookups, (: calculations employing the Lookup Function (my odds-on favorite), or © scripted routines ... allowing a "starter set" for the Commission profile, if you will....

Then it seems ...

• the Commission profile residing in the Invoice could be edited to suit a given Invoice, when such custom-tailoring is warranted, and

• the Commission profile would be "preserved" in the same sense as we often do with, say, a Billing Address, so that it remains static and represents a "snapshot" at the time of the transaction, and

• Commission records could be generated from Invoices as the profile is applied against the constituent Line Items, and

• Yet Commission records could alternatively be generated from the related Line Items, if doing so would more easily satisfy the requirements of the overall scheme.

... would that work? I'm likely overlooking something (convenient, isn't it?), and I can kind of discern through the mist a role for an Agreements table where the Staffperson's Commission profile is joined to a given Customer and thereby serves as the "source" profile, but I think this is all I've got at the moment.

I, too, have faced the "hydra effect" of linking the necessary elements through a seemingly increasing number of Table Occurrences. On a good day, it makes me take a couple of steps back and considering taking a different tack.

[LaRetta]

Thank you so much for your input!

There are many things I've left out (to shorten the post). Here are a few which might clarify why I'm approaching from a Agreement per Staff approach instead of Invoice/LineItems level:

1) We also write/post daily - I said monthly because many of the rules are cumulative and additional run will have to happen monthly - looking at full month, prior full quarter, prior full fiscal (if Rep has sold more than $500,000 the first 3 months, they fulfill Provision #6 of their Standard Agreement and get additional bonus). If bottle was paid as NewSku in September (NewSku Provision #3) but returned in October, script needs to write, backing out that NewSku commish and ALSO negating 'system awareness' of September sale so when customer is again sold that product it would THEN again be counted as NewSku (current NewSku table occurrence fails here) and I manually adjust (PIB). How do I write the result of that rule to an Invoice or LineItem in that moment when it would actually affect TWO Provisions particularly when it spans backwards to a prior Invoice/LineItem? It may be backout of NewSku Provision #3 AND be current Promotion from Products. So yes, it's realtime but also not.

2) Each Sales Rep is assigned 5,000-20,000 customers. I didn't want to deal with Agreements on Customers level (for many reasons); started that direction and it would be nightmare to maintain. I haven't even told you that Provision will be dependent upon Customer type (whether Chain and so forth) because I didn't want to muddy an already muddy post). And those are details I can handle from the Provision/script end.

I've spent 6 months considering this approach (gone to bed with it) and I'm clear on the Provisions needed. The Provisions ARE my calcs (in essence) but I can't give Owner field definitions and my calcs alone don't work because of adjustments and returns AND calcs must be hardcoded (can't flex per Rep, per timeframe and so on). Also, different Reps may have LineItems on same Invoice - so process will sometimes consider TWO Agreements, ie, Provision #1 for Customer-assigned Staff (they would get Gross Sale Invoice Commission) and Provision #3 for staff who made the sale (they would get the bottle commish or Promotion) so one LineItem can generate two or three Commission pieces (Provisions) as records or filling in multiple fields (?). So I need to take my current scripts and calculations and turn them into hardcoded browse calcs as records/fields with Owner ability to 'fill in the blanks.' I will have template of Provisions, so that Owner can custom build his 'per staff' Agreements.

I'll pull your ideas into my process and seriously consider how they might work here - you possess clarity of thought and what you've said makes good sense. My twist is: How to allow Owner to write Provisions. I can write my scripts to use Provision fields/records once I know what Provisions should look like. And how to write to Commissions (whether fields/records) ... some Provisions are based upon dollars (invoices), some based upon counts of bottles (lineItems) and some are based upon number of new customers. How can that appear in ONE commission table for Owner manipulation? Or can it?

UPDATE: I would be willing to use FIELDS within Invoices or LineItems but I think I'm dealing with 1:n here; otherwise, I'd have to add MANY fields to each table. These tables already have 100+ fields each and I didn't see the need to keep commissions within these tables directly - particularly when they span Customers, Invoices AND LineItems.

L

Edited October 5, 2006 by Guest
Added update

[LaRetta]

Yes, ThatOneGuy, this is helping me approach from a different perspective. I *think* that Provisions must be tied directly to Commissions. Why? Example: My current break in process is NewSku. If product hasn't been sold in last (2 years) then they get NewSku commish. I use TO on DateShipped AND ProductID to isolate when it was last purchased. But if there is special circumstance and Owner doesn't pay NewSku commish on it, then my process breaks because that sku quantity still stays in LineItems and *thinks* it's a NewSku and won't pay the Rep the NEXT time it's ordered. Provisions should be looking to Commissions instead; where qualifying product would list. If criteria is met, calc can be used to figure commissions. So ALL qualifying Customers, Invoices and LineItems should write to it according to Provisions. And ALSO, Provisions should use Commissions table when looking back in time! This is a major piece I missed!

If Owner can adjust these figures, my scripts xor calcs can't use the real data any more! So Provisions should write ALL qualifying items. Maybe. That's all I've worked out and I shall continue to sort through it. Thank you again!

Oh! Different tack! Yes! That's what I'm seeking and I'm coming around the backside of it now!

Doh ... Lookup FUNCTION ... I haven't used it yet. I will study it further as well.

Edited October 5, 2006 by Guest
Added Oh and later Doh (blush)

[ThatOneGuy]

you possess clarity of thought and what you've said makes good sense.

Lucky shot. In fact, looking back on my first post and considering your skill level, I don't think I imparted anything you didn't already know.

I'll likely get "dinged" by other, more-accomplished forum members, but I'm gonna take a gamble here. First, nothing qualifies me as a relational theorist, so my remarks fall more toward relational philosophy. Second, you probably already know all this, as well. Finally, the post is lengthy, but you kinda opened the door in that regard, didn't you?

When I face a complex system ... and yours may exceed what I've done ... I think in terms of a nuclear analogy. Database veterans focus on granularity, and I couldn't agree more. If we break down our collection of data ... and if we do it well ... we can isolate elements. If I'm down with granularity, this looks a whole lot like tables to me.* Granularity is the essence of a normalized data structure, but it opens the door to so much more.

The beautiful magic occurs because we can then recombine these elements** in numerous ways to depict the dynamic interaction of data. If you're still awake, I would submit that such an intersection of data represents a nucleus. In a mature database, nuclei are visible on the Table Occurrence Graph.

Line Items always emerge as a nucleus in my solutions. Related datum from Customers, Invoices, Quotes, Vendors, Products, Prices, Staff all comes pouring into an Item record. ***

I think that Provisions must be tied directly to Commissions.

And I trust your instincts ... yet I'm having a hard time escaping the gravitational pull of a model where the Commissions scheme is also tied into Items, particularly once you mentioned different Staffpersons can contribute Line Items to the same Order. There must certainly be other clusters of TOs (nuclei) that depict additional operations of Commissions and its interaction with Provisions. Still, it seems that Items drives Commissions ... once it's been smashed against Provisions. Perhaps a Provisions TO off the backside of an Items__Commissions table occurrence. (If that's true, I wonder if Commissions is more like a join file ... then I wonder if the distinction even really matters.)

NewSKU, huh?... great

My current break in process is NewSku. If product hasn't been sold in last (2 years) then they get NewSku commish. I use TO on DateShipped AND ProductID to isolate when it was last purchased. But if there is special circumstance and Owner doesn't pay NewSku commish on it, then my process breaks because that sku quantity still stays in LineItems and *thinks* it's a NewSku and won't pay the Rep the NEXT time it's ordered. ... If Owner can adjust these figures, my scripts xor calcs can't use the real data any more! So Provisions should write ALL qualifying items. Maybe.

Wow, where to start? Well, from the TO for Items, would you consider another table occurrence of Items with the relationship predicates being (1) CustomerID = CustomerID and (2) ProductID = ProductID and (3) ItemID ≠ ItemID to exclude the current record and (4) the DateShipped predicate you need? If so, a Count greater than zero of any records satisfying the relationship indicates the NewSKU provision should not be applied.

But something's missing ... whether the Commission was actually applied or whether management suspended it in this case for some reason. Can you absorb another field in Items to hold the CommissionAmount? It would have to be a "stored" value. (Would the lady care for the Lookup Function? It is today's special, and it is quite delicious!) This would allow for a 5th predicate in the relationship turning on whether NewSKUCommission is appropriate.

If you're okay with all that, there's something I've learned the hard way about FM relationships constructed on inequalities such as "greater than" and "less than." They're slow! I've had solutions suffer a big hit on speed. So, a relationship predicate like "Commission > 0" might be problematic. I was able to solve my problems and increase speed by creating a calculation field resulting in a Number with the argument being "GetAsBoolean (targetField)." The relationship predicate becomes "boolCommission = 0" where the zero is a constant on the parent side of the relationship, and where it can be either a global or a non-global, your preference.

These "little boolean switches" can filter or sift a remarkable amount of stuff, and it may be just the ticket for your "NewSKU vs Commission" scheme. They perform much of the heavy-lifting in my solutions, since we have no other way in the relationship definition to capture when something is "missing" or just plain "there." ****

Yes, ThatOneGuy, this is helping me approach from a different perspective.

I guess we're gonna see if you still feel the same after all my vacuous tripe. I only hope we get you further down the line.

------

* For those indulging the analogy, if an atom is the smallest possible particle of an element that retains its chemical properties, perhaps those are records ... but I ingest. )

** For the critics, yeah, I know ... it is atoms that are combined, not elements per say, and it is a molecule in which they result.

If anyone would care to enlarge on this analogy and flesh it out more fully (without hijacking LaRetta's thread) I would really enjoy reading what you could come up with. There's a ton of stuff to work with: electrons, protons, neutrons, ions, orbitals, isotopes, fusion, fission, radioactive decay (smells like "file corruption," doesn't it), and much more. So if you have a quirk for quarks....

*** It seems like once or twice a week, we get posts in the forums from users trying to figure out how they can generate reports or print invoices or such. You know, it's all really about output, and the Items model I'm describing is a worthy vehicle. Output is much easier when we switch to a Layout where that set of related child records can be isolated. When we have our relationships right ... like, that nucleus thing ... the intersecting data can be "pulled" into the Item record. One other thing: it doesn't bother me if my Items table has "a lot" of fields. Since the Items table is essentially "where the rubber meets the road," it seems natural.

**** For those keeping score at home, there is an important distinction between IsEmpty and GetAsBoolean. While IsEmpty will tell us whether there's "anything" in a field and produce a "1" when the field is populated and a "0" otherwise, GetAsBoolean returns a "0" when the field is empty or when the value contained in the field equals Zero. Otherwise, it returns a "1."

[comment]

While IsEmpty will tell us whether there's "anything" in a field and produce a "1" when the field is populated and a "0" otherwise, GetAsBoolean returns a "0" when the field is empty or when the value contained in the field equals Zero. Otherwise, it returns a "1."

I am afraid that's not entirely accurate - although you could that impression from reading the help.

But in fact, GetAsBoolean("abc") returns 0.

---

P.S. Is it my imagination only, or has the quality level of the help dropped sharply between 7 and 8?

[ThatOneGuy]

But in fact, GetAsBoolean("abc") returns 0.

Wow, Mike, you're right! I hadn't encountered any problems because I generally reserve the GetAsBoolean function for numeric fields. For all other field types, I tend to use the IsEmpty function in the argument. Thank you for the correction.

As for the quality of Help between FM7 and FM8, I haven't noticed any deterioration.