FM - Design Issue

This is related to my last question.  I have a LeaseWriter table and layout where the main body of the leases are written.  Then, I have a LW_ClauseMaster table with an x relationship, 1 rec, and about 120 fields that contain lease clauses.  I make these lease clauses chooseable by have a YesFlag field from a ClauseFlags table, which is related to the LeaseWriter table (not the LW_ClauseMaster table).  If this is confusing, see the screen print.
 
With my last question, I was able to convert the 120 fields into 120 records (in ClausesbyRec table) in order to display these fields alphabetically in a portal.  While that helps for display, I don't see how I can make a certain record/clause show in my LeaseWriter calc fields whereas it is easy when it is a field (see 2nd screen print).  Additional complexity is that I'm not sure how to relate LW_ClauseFlags to clauses; I just put them side by side and check Yes makes the clause text show up in the "Chooseable" calc field (see 3rd screen print).  How would you do this so there is only 1 table to keep updated?
-Relationships.jpg
-ClauseFlagNexttoClauseText.JPG
-TypicalClauseExportFieldinLW.JPG
-ChooseableExport.JPG
LVL 2
rvfowler2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will LovingPresident, Dedication Technologies, Inc.Commented:
Perhaps structure the process a different way. I presume you're using the calculation to build the Lease into a single calculated text field. One way to do it would be using a script to test for each part of the optional Clauses. Give each Clause a ClauseID (serial number) and using Set Field steps set to something like:

If [ pattern count( BizType = "Bar" ]
   Set Field[ ClauseIDSelect_g ; 33 ]
   Set Field[ LeaseText ; List( LeaseText ; ¶ ; ClauseSelected::ClauseText ) ]
End If

The LeaseText field could be a Text field, regular or global. The first Set Field inserts the ClauseID into a global field which is the Primary key for a relationship to the Clauses. The second one updates the LeaseText field to contain whatever exists there already plus a new carriage return and the related Clause text.
rvfowler2Author Commented:
I was never big on scripts, etc., because moving parts seems to fail.  I prefer static calc fields or relationships when possible.  I'm not sure your example would account for someone unchecking a field, would it?
Will LovingPresident, Dedication Technologies, Inc.Commented:
You can simply test using isempty() or not isempty() in your If Statement for a particular field

For a calculation, you could populate a Global Text (using a script) or Calculation field with a List of the ClauseIDs (carriage separated). If you make another calculation field with List( ClauseIDsGlobal::ClauseText ) it will give you a List of all the Clauses for which you had IDs. The trick will be to get them to sort correctly but you could have an Order field for each Clause and set the relationship to Sort by Order.

The reason to build a Lease using a script rather than a calculation is the same reason you copy the current name and address fields into their own fields for an Invoice rather than use the related Customer fields. You want to know what the Billing address was when the invoice was written, not what it is now. Similarly with a Lease, if you update the text for a clause at some point in the future, the Lease text will only show what the current Clause values are. Building a Lease in a text field using a script, or at least copying the result of a calculation into a text field, gives you documentation of what was actually sent to the tenant, not what your possibly revised Clauses or Lease calculation currently read.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

rvfowler2Author Commented:
Good point and it is  the same principle as if you use a calc text field or an auto-enter text field.  The former will always replace so the danger is losing your original; however, the latter can accidentally be overwritten and we have had a lot of data entry errors here.
Will LovingPresident, Dedication Technologies, Inc.Commented:
Yes, exactly, a dynamic calculation vs auto-enter by calculation. My suggestion would be a documents table that automatically disallows edits after a certain time after creation or to certain users. View, Print, yes, edit, no.
rvfowler2Author Commented:
Thanks, Will.  This is enough info to solve the problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.