Best Practice For Transferring Field Data Between Tables

[Flappy]

I am trying to sort out what the best way to transfer the data from fields in one table, to fields in another table. For example; I have a Cash Register Table, where Cash transactions are performed, and the results of those transactions are transferred to the accounts receivables Table. There are three strategies as I see it.

 

1. Use The transaction ID Field in the cash register as a match field in a relationship with the Accounts Receivable table that is set to create records in Accounts receivable, then use a series of "Set Field" script steps to Fill in the field data.

 

2. Set a series of variables in the Cash Register Table, GoTo The Accounts Receivable Table, Create a New Record, Use a series of "Set Field" Script steps to fill in the field data with the contents of the variables.

 

3. GoTo the Accounts Receivable table and import the contents of the Cash Register Transaction.

 

This is the simplest example I have, there are several others where the amount of data is larger and more complex. I have tried all three, and the import option seems much faster, and easier to script, but I think I read somewhere that it is not a good strategy in a multi user environment. I would appreciate any insight I can get.

 

Thank you

[doughemi]

Set the layout in Cash Register to have an OnRecordCommit script trigger.  Create a script to generate a new related record in Accounts Receivable and populate the fields with the appropriate SetField[AR::SomeField; CR::RelatedField] steps

[LaRetta]

Why are you moving data at all?  That is what relationships are for.  Every time you use script to write data from one location to another, you risk a break.  And when dealing with financials, that is the WORST time to experience a problem.

 

Instead I suggest that you write only the TransactionID and then access the values required through the relationship.

[Rick Whitelaw]

What LaRetta said. Accounts Receivable is likely an unnecessary data point.

[Flappy]

Our "Cash Register" functions as both simple cash intake, but also as a payments point to receive deposits on orders, and payments on invoices. When the cash register is brought up, there is order references, and account information that is viewed ( open invoices, etc). I do the calculations needed there, then store the result in AR. 

 

Rick- please clarify what you mean by "Accounts Receivable" is an unnecessary data point. My solution is used for my screen printing shop, and has worked very well for it's first 8 months. We have used Filemaker for many years for our production jobs, inventory, etc., but this is my first shot at keeping track of money coming in. Just because it works, doesn't mean it's right, so I am all ears if there is something "not so smart" I am doing.

 

LaRetta-The reason I move data is so that the person doing the order or transaction can make a mistake, and cancel their transaction without changing the final transaction data that is stored ( this is much more in orders than the Cash Register as people buying shirts change their minds frequently and there are always edits and changes to be made. I don't see another strategy, but if there is one, I would love to know about it.

 

Thank you both for your time-

[LaRetta]

The reason I move data is so that the person doing the order or transaction can make a mistake, and cancel their transaction without changing the final transaction data that is stored ( this is much more in orders than the Cash Register as people buying shirts change their minds frequently and there are always edits and changes to be made.

 

Hi Flappy,

 

You are correct in keeping all Transactions in same table.  I suggest that you instead figure out how to allow Users to make a mistake but cancel their transaction.

 

There are many techniques available.  The simplest is to place a button (but do not assign it ) or even web viewer over the entire layout.  This stops ability to Commit because the User can't 'deselect', i.e. can't click onto a blank spot on the layout.  Then place a SAVE button which runs script which asks if they want to save or revert.  Saving just commits.

 

But you can also use modal window or triggers, and you may wish to use the transaction model; it is difficult to be specific without seeing your exact setup.   It sounds like it also might help to tighten up on User privileges, possibly use custom menus and generally add more protections to restrict improper access.

 

At a point, the entry should be locked down and future change prohibited (guaranteed by using extended privileges).  To give perspective, that would be the point when you decided to copy it to AR.  Then you can use the tricks above (and others) to guide Users through their entry while still allowing graceful back out if they make a mistake.  And you will have the hard-line protection from Security even if they DO get past your nice guidelines.

[Flappy]

Hi Laretta-

One thing in my favor is that we are avery small shop. We have a total of ten users, but only three of us input quotes & orders. One user is for purchasing, one for accounting, and the rest are essentially view only production types ( looking up job info etc). Can you briefly explain the "Transaction Model" you mentioned ? Is this essentially like a separation model ?

 

Thnaks-

 

Skip ( Flappy)

[LaRetta]

Transactions are explained here by Todd Geist:  http://www.modularfilemaker.org/2013/04/transactions/

 

I am sure you choose the best method for your specific situation after reviewing each of them, Skip.  

[Flappy]

HI Laretta-

I enjoyed exploring the website and as it turns out, the transaction model is basically the way I have things set up ( though not nearly as refined), I think I just explained it badly. The modular concept is really interesting and needs further exploration.

 

Thanks for your help-

 

Skip