Problem creating new records from found set

[Valdrin]

Hi all,

What I am trying to do is create a script that will add the current found set of customers to a chosen group.

I have the following tables:

CustomersTable

kCustomerID

JoinCustomer_GroupsTable

kCustomerID

kGroupID

GroupsTable

kGroupID

I know that I need to grab the kCustomerID for each record in the found set and place it in a new record in the JoinCustomer_GroupsTable (along with the kGroupID of the group I am trying to attach them to), but I just cannot seem to make the script work right.

Thanks in advance!

[Søren Dyhr]

If you need it done very swiftly, then:

Go to Layout [ “GroupsTable” (GroupsTable) ]

New Record/Request

Set Variable [ $newID; Value:GroupsTable::kGroupID ]

Go to Layout [ “joinCustomer_GroupsTable” (joinCustomer_GroupsTable) ]

Import Records [ Source: “file:Untitled.fp7” ] [ No dialog ]

Set Field [ joinCustomer_GroupsTable::kGroupID; $newID ]

Replace Field Contents [ joinCustomer_GroupsTable::kGroupID; Current contents ] [ No dialog ]

Go to Layout [ original layout ]

What is used here is import between tables in the same file - which obviously requires correct mapping ... which have to be checked each time you add a new field to either of the involved tables!!

--sd

[Valdrin]

That does add the files quickly (I tried it with a found set of 2000 records). It is not what I need for this project, but I will remember it for future use.

For now I need a script that will survive the users playing with the fields, and will let them add the found set of customers to preexisting groups of their choice.

[comment]

I need a script that will survive the users playing with the fields, and will let them add the found set of customers to preexisting groups of their choice.

That's not quite clear: presumably, they will be in a layout of Customers to create the found set. What will they use to select the group/s they want?

Not sure what you mean by "users playing with the fields". Surely, users shouldn't be allowed to add/delete fields?

[Valdrin]

Not sure what you mean by "users playing with the fields". Surely, users shouldn't be allowed to add/delete fields?

Once I hand this solution off they told me that they might end up adding a field or two.

That's not quite clear: presumably, they will be in a layout of Customers to create the found set. What will they use to select the group/s they want?

This is one of the reasons I am having such hard time with this script.

What they want to be able to do is create an unlimited number of groups, and be able to place eace customer several groups. They have about 7000 customer records and, really want to be able to add found sets of costumer records to the groups that they have created.

So I created a text field called JoinGroup in a CustomerTable based layout and made it a pop up menu with a value list based on the GroupID from the GroupsTable. I want to place two buttons next to that field, one that will add the current record to the group chosen in the value list, and another that will place all the records in the current found set in the group chosen by the value list (the latter with a custom dialog step to make sure that they really want to add the entire current found set).

Any and all help with this script will be greatly appreciated!

[comment]

Once I hand this solution off they told me that they might end up adding a field or two.

And who's going to fix the things they break in the process? As Søren mentioned, import mapping is affected by adding/deleting fields. This applies not only to the script suggested by Søren.

I would have suggested the same thing as Søren did - but if you want, you can loop through the found set, adding the join records one-by-one, roughly:

Set Variable [ $groupID ; Customers::gGroupID ]

Go to Record [ First ]

Loop

Set Variable [ $customerID ; Customers::CustomerID ]

Go to Layout [ JoinTable ]

New Record/Request

Set Field [ JoinTable::GroupID; $groupID ]

Set Field [ JoinTable::CustomerID; $customerID ]

Go to Layout [ original layout ]

Go to Record [ Next, Exit after last ]

End Loop

This could probably use an improvement in the form of checking if the customer is not already a member of the group.

[dansmith65]

I would approach the entire situation slightly differently...

I would manage the members of the group from a layout based on the group table. Display a portal of all the customers in the selected group. Have a button that says "Add Customers", which opens a new window based on the Customers table and allows the user to perform a find, select a single record, or all records in the found set. Then when they click a "Select" button in the new window, use a script similar to what Comment gave you.

If you use this type of method, check out this file which you can use for selecting records: http://fmforums.com/forum/showtopic.php?tid/202233/#341219

The scripts in that example file will give you a value list of kCustomerID's that need to be added to the JoinCustomer_GroupsTable. Then you just need to create a looping script to create your records.

If you will be dealing with large groups, it would be a good idea to do some performance testing of your looping script. For example, I sometimes use a relationship just for creating records in a related table, but I don't know if the performance of creating records that way is better than by going to that table, creating a record, and setting the two ID fields.

P.S. if you are interested in using this method let me know, because I have updated the scripts I used in that example file, but haven't posted the updates yet.

[Valdrin]

And who's going to fix the things they break in the process? As Søren mentioned, import mapping is affected by adding/deleting fields. This applies not only to the script suggested by Søren.

I know, but all I can do is warn them of the danger.

I would have suggested the same thing as Søren did - but if you want, you can loop through the found set, adding the join records one-by-one, roughly:

Set Variable [ $groupID ; Customers::gGroupID ]

Go to Record [ First ]

Loop

Set Variable [ $customerID ; Customers::CustomerID ]

Go to Layout [ JoinTable ]

New Record/Request

Set Field [ JoinTable::GroupID; $groupID ]

Set Field [ JoinTable::CustomerID; $customerID ]

Go to Layout [ original layout ]

Go to Record [ Next, Exit after last ]

End Loop

This could probably use an improvement in the form of checking if the customer is not already a member of the group.

Thanks for the help -- even though the import runs faster, it is probably safer to use the loop.

[Valdrin]

I would approach the entire situation slightly differently...

I would manage the members of the group from a layout based on the group table. Display a portal of all the customers in the selected group. Have a button that says "Add Customers", which opens a new window based on the Customers table and allows the user to perform a find, select a single record, or all records in the found set. Then when they click a "Select" button in the new window, use a script similar to what Comment gave you.

If you use this type of method, check out this file which you can use for selecting records: http://fmforums.com/forum/showtopic.php?tid/202233/#341219

The scripts in that example file will give you a value list of kCustomerID's that need to be added to the JoinCustomer_GroupsTable. Then you just need to create a looping script to create your records.

If you will be dealing with large groups, it would be a good idea to do some performance testing of your looping script. For example, I sometimes use a relationship just for creating records in a related table, but I don't know if the performance of creating records that way is better than by going to that table, creating a record, and setting the two ID fields.

P.S. if you are interested in using this method let me know, because I have updated the scripts I used in that example file, but haven't posted the updates yet.

I am very interested in this method, it fits in well with some of the other stuff I'm doing.

[dansmith65]

Here is version 4 of the example file; changes are still in progress, but once I have made all the changes I want to, I will be posting it in the original thread. In the meantime, if you do implement it, you'd be better off using this version than the last.

The 'RecordSelection__View' script is just a sample of what you can do with the 'RecordSelection' script. You could probably modify the 'RecordSelection__View' script to do what you want since it already loops through all the selected record ID's.

RowHighlightSelectionV4.zip