Set Field By Name using a variable

[davidrichards]

I am having a tough time getting the set field by name script step to work and hoping someone can help me with this.

I have a file of sales transactions over many years.  I have a related file for customers.  The customer file has counters for how many times a customer visits each year, how many suits, sportcoats, etc. they buy each year ( visits_2017, visits_2016, visits_2015, suit_count_2017, suit_count_2016, suit_count_2015 and so forth). I would like my script to evaluate the year of the transaction in sales and update the appropriate counter in customers.

I have been unsuccessful in defining a variable and set field by name combination to accomplish this task.  Any recommendations?

[doughemi]

The first recommendation is to correct your structure. Then you won't need set field by name, and a lot of other gyrations.

There should be 3 tables: Customers, Product, and Transactions. Transactions is a join table related to Customers by Customers::ID =  Transactions::CustomerID and to Product by Product::ID = Transactions::ProductID. Each record in Transactions is one purchase of one item by one customer (e.g. Customer 1234 bought 6 suits (id 9876) on 3/21/17. If the customer also bought sports coats on that date, that purchase would be a separate record. 

Now you can easily make reports on product sold, customer purchases (per year or per product), customer visits, and most everything else about the business.

[comment]

I would recommend you change your structure and use individual records in a related table instead of the fields. Let the table have fields for CustomerID, Product, Year and Quantity (based on your example given above, you can add more categories as required). Then you can update the desired record either by finding it or by using a relationship matching on the relevant fields.

This is assuming you really want to update a "counter" every time [something] happens, instead of simply creating a record of that something, and producing a summarized report of these records when needed.

--
P.S. Please don't send me private messages asking me to look at your post. 

Edited April 26, 2017 by comment

[davidrichards]

I realize I need to give a fuller overview of this project.  The overall goal is to study customers, not to track merchandise.  I am importing sales records from our inventory system and using filemaker to study customer patterns.  There is a file for customers (shown below), and a file for transactions, a file for referrals. Each of the files are joined by customer number.  I am looking at customer behavior over a minimum of 10 years back and maintaining the sales statistics on an ongoing basis.  It is important for our sales associates to see the statistics on the fly as a customer walks through the door and browse through the customer file. I will be running reports reviewing customers behavior based on these fields. Lastly, I am concerned about using relationships for each year and statistic as there are 11 items per year * 10+ years.  I am importing about 18,000 customers and 450,000 sales transactions.

My script currently loops through each day's sales and adds to each field appropriate with a set field command.  It would be very helpful both for the initial load of 18 years of data as well as future work to be able to use a variable to identify which field needs to be updated.  The problem with this solution is that once a year I will need to physically move fields on the layout to put the appropriate year's field in place (and shift the old fields down).  I know the relationship concept would solve that problem, but, I'm concerned about having too many relationships.

Attached are screen shots of the customer view (the customers individual transactions and referral information is below the shown section - no need to include those items in this question) and the basic script updating fields directly with set field (rather than set field by name).

 

[comment]

3 hours ago, davidrichards said:

The problem with this solution is that once a year I will need to physically move fields on the layout to put the appropriate year's field in place (and shift the old fields down).

Yes, that's exactly the point here. Filemaker is a database, not a spreadsheet. You cannot add columns at will (not as a user, anyway). 

 

3 hours ago, davidrichards said:

I know the relationship concept would solve that problem, but, I'm concerned about having too many relationships.

I don't see why you would need more than one relationship, based on CustomerID, to allow you to view all the summary values for the currently viewed customer in a portal (or several portals, filtered by year and/or by product). 

 

3 hours ago, davidrichards said:

I will be running reports reviewing customers behavior based on these fields.

A report concerning more than one customer would be likely produced directly from the table containing the summary values. Note, however, that Filemaker's inability to add columns dynamically also means it cannot easily produce a cross-tab report. 

 

 

[davidrichards]

Thank you again for your help.  Based on your comments, I have added:

1.  Calculated fields in Sales (one for each department - suit count, sportcoat count, etc) which holds 1 if the transaction is a sale, -1 if a return.
2.  Relationship between Customer and Sales based on Year&CustomerNumber
3.  Defined the "TY Suits sold box" as Sum(SuitCount) - using the Year&CustomerNumber relationship

I will have to create additional relationships for each year to be evaluated (this year to (this year - 10) for a 10 year study.

Is there a more efficient way of doing this?
Do you have any idea how well this will operate with 18,000 customer records?
My goals here are to provide our sales associates a great visual display of a customer's purchase pattern and search for customers who are breaking their pattern (that is, if a customer typically shops once a year and we haven't seen him in 13 months, we should give him a call).

[comment]

I am not sure how to address any of these questions. There is a big difference between showing detailed information about one customer at a time, and producing a report concerning all (or some, but more than one) customers. The latter also depends on how often you will be doing this, and how long are you willing to wait for it.

In general, I would disqualify any solution that requires a periodic modification of the schema.