duplication of data, script variables, and lookups?

[Tim Falconer]

I'm on a roll tonight... I hope I'm not overstaying my welcome here , and also that this question is not a "tl;dr".

A friend had a glance at one of my scripts and expressed concern at the number of variables I was using in a script to write values into my "Shipping Line Items" record (maybe 10 variables, all local).

At some point, she had said "Lookups are useful where they are useful, but using variables is OK where it's appropriate." It appears that maybe I took some undue liberties with this...

In this case, the source data is in one of two portals at the top of the layout. The user can "Add Line Item" to the Shipping Line Items table from either portal.

Depending on whether the user adds a line to Shipping Line Items from the first portal or the second portal, the data (including a few "entry fields") comes from the portal line in that portal. The new record contains 8 or 10 fields of data because it's a record of what was scheduled for that shipment. Since the data for each field can come from either of two other tables, a simple lookup does not make sense. (Of course there are ways around that, but one hopes to find the most straight-forward, maintainable, and simple solution)

This solution I'm working on does a lot of this: it creates records whereby we track transactions, from Quotes to Jobs, from Jobs to the Production Floor, from Inventory to the Production Floor, and from the Production Floor to Shipping.

Is there anything you can clarify about when it's appropriate to use a bunch of variables to create such transaction records and when it's not? Is there a rule of thumb: "use lookups first where appropriate"? Or is there some other process of which I don't yet have the concept?

Am I thinking of the whole concept of creating transaction records wrong, somehow?

I am under the impression that a complete record of a transaction should contain all of the data which pertains to it, not relate to data in a different table. For example, here, my Shipping Line Item contains particulars about the parts in the Shipping Line Item, even though the data which is not unique to the Shipping Line Items is also stored in one of three other tables: Job Order, Job Line Items, and Part Numbers. Likewise, the Shipping Order (which is the parent of the Shipping Line Items) then creates one or more Packing Lists, and optionally, a Bill Of Lading. All of the data for these is in the Shipping Order, but I'm copying it into the Packing Lists (and where needed, some of it into a Bill of Lading). I'm doing this because I think that it's the right thing to do, to have "real records" of what happened. Those records are then locked. If the data is in different tables, it makes it harder to lock it... right?

Not having done this before, I don't know if it's really the right thing to do or some total novice bonehead mistake.

Thanks in advance for putting up with me :)

[jbante]

You're doing fine with variables. As you've already figured out, lookups can only refer to one related source table each. If you need data for a given field to come from different sources in different situations, lookups are not for you. I can't remember the last time I used a lookup, and I've known some developers who advocate never using them as a matter of principle.

Using variables in a scripted record creation process makes it easier to see what data is moving from where to where, in my experience. It can be confusing to maintain solutions where some data movement is defined in the schema (Manage Database...) and some is defined in scripts.

The counterargument for managing all the data movement with variables in a script is that if you make a different script for creating the same records, you may wind up duplicating some of the business logic for what data comes from where that could be maintained in one place in the schema. If you find yourself doing that, I still recommend keeping the $variable based workflow. You can make one script that handles all the business logic the two operations have in common, and another for what's different about each operation. You can call the general- and special-purpose scripts in succession (I prefer this option), or make the general script a sub-script of the special-purpose scripts.

Two other ways for moving data between tables on record creation are auto-enter calculations and between-table imports. Auto-enter calculations would let you put the logic in the schema, plus the flexibility to reference different related tables in different situations for the same field that you don't get with lookups. Imports can be faster that transferring data with $variables, though it can make for a slightly more difficult-to-maintain script, depending on the complexity of your data flows, and it doesn't accomodate transforming data during movement as well as auto-enter calculations or $variables.

[LaRetta]

Another consideration is whether you need to move the data at all; eliminating redundant data is the usual goal. Since you mentioned lookups, it suggests that the tables are already related. So here are some additional considerations if the tables are related or you want them to be:

1. Cross place fields - simply place the parent fields on the child layout; no need to duplicate. This works well if you want the data to change if the parent data changes and if the parent is the 'one' side.

2. Copy only the ID to a variable, go to the new table ( behind a Freeze Window ) create new record and set the parent ID in the child with the variable, commit the record, then set any other fields directly as Set Field [ child::parentAddress ; parent::Address]. This works well when you need to plant data and have it remain as it was at the time (you want parent address at the time of shipping the order and not have it change if later their address changes). The only time I would write all data to variables is if the tables were unrelated.

"I am under the impression that a complete record of a transaction should contain all of the data which pertains to it, not relate to data in a different table."

I would suggest the opposite ... data should be displayed through relationship unless the data might change and it's value at the time must be preserved. Shipping would fall into this category for customer address, or Order LineItem with Product Price ... each situation and field should be considered individually.

[bruceR]

And of course there are auto-enter calcs, which can function similarly to lookups, but have more options (including calling the "lookup" calc function!). I agree with the others that generally it is NOT a good idea to "flatten" the data into an item record.

[Tim Falconer]

Thank you all so much. I've "+1'd" each of your comments but that does not come near to expressing my gratitude. And I've been lurking here for a few months, and have already learned much from each of you by reading other threads.

Thanks so much!

tzf