Line 3 - you are clearing the field "active brews list" by not setting the field with any content.
Line 6 - you are not setting a variable because "active brews list" is now empty.
Line 11 - Since $newActiveBrew has no content, nothing is set.
Possibly move line 3 until after line 6?
Also, learn how to use the script debugger and data viewer in fm pro advanced. These issues are easily identified by seeing (for example) that line 6 did not successfully set an active variable.
If I understand what you're doing correctly, you want Customers::Active Brews List to contain something like this:
This shouldn't be necessary. Since you can make a relationship between a return-delimited list and a corresponding field in another table, you should be able to do:
and join that to the Brews table's ID field. Then List ( Brews::Beer Name ) will look like:
This is called a "multi-key". It's a basic principle of relational design that you shouldn't keep data in more than one place. What happens if you change the name of a brew at some point in the future? All the records that had the old value would have to be updated, too.
If, on the other hand, you wanted (for historical purposes) to know what the brew names were at the time the tracking record was created, then you could use a lookup or auto-enter calculation to copy List ( Brews::Beer Name ) into a separate field in the tracking log table. Or you could use ExecuteSQL to enter it all in a single field (although this makes it more difficult to extract). Or you could use one of a number of functions to concatenate the two lists into a single list.
The really "proper" way to do this is not to use a checkbox set at all, but a join table instead. You have a many-to-many relationship between Customer and Brew: One customer can have many brews, and one brew can be associated with many customers. In such a situation, the standard practice is to use a table in between the two where each record represents the unique combination of Customer and Brew. You'd have, at a minimum, the customer ID and the brew ID in that table, and create the records via either scripting or pull-down menus (depending on your workflow).
Just some food for thought. HTH
Just wanted to add this for completeness' sake:
The really "proper" way to do this is not to use a checkbox set at all, but a join table instead.
If the only single piece of information you want to store is the association itself (a person is somehow associated with a brew, but you don't want/need to elaborate on that association any further), a list in a field will do.
As soon as the association has attributes of its own, a join table is the proper way.
It violates First Normal Form to have multiple facts in a single field. That’s what I meant by “proper” (in quotes).
However, we all do it at various times, so “proper” will often give way to “expedient”. (That is, the view isn’t worth the climb.)
The beers will be changing periodically in those slots. So if in the event if slot 1 is Budweiser at one time and now slot 1 is corona. That's fine. If the beer name changes the checkbox will still be checked cause the beer name was the only thing that changed not the slot number . The purpose of the tracking log is to record what beer that customer drank at the time . So if slot 1 was Budweiser ill know at this date the customer drank this beer, regardless if slot 1 is different at a later date. I'm tracking the completion of slots however for historical data purposes the customer should know what they drank. So this is why I need the beer names to be sent to the tracking log table.
I thought that might be the case. You can use one of the several methods I gave you to record that information for historical purposes.
Have you tried using the script debugger and data viewer to see where it's going wrong?
Line 4 is your problem. It will only fetch the list of brew IDs, not the list of IDs and names you want.
Please read through my former post in order to fix the problem. It's not going to be a "tweak three script steps" fix. You're going to have to do more than that.
I do have a relationship between customers and active brews with a field in both titled actve mugs club but its not by beer ID. This is why.
In my active brews list its 1-100 ok. So a bar has the option to choose up to 100 beers for a mug club. However some bars may choose 50 or 60. Within that layout there is a yes check box. If yes is checked then it means that slot is active for the mug club. The relationship between customers and active brews is from a field titled Active Mugs Club. So if the field is YES then it will display in the value list field. Basically I'm filtering my value list to show only the slots that have YES. This is the relationship.
I know line 4 is my issue but knowing the relationship I have setup now. What is the best way of doing this out of the options in your former post ?
I have done the debugger Mike Beargie.
I’m not talking about the main database. I’m talking about in your history table / log.
and you checked the data viewer after each step to see if a variable was created with the content you were expecting?