copying set of records from one table to another?

[asfasf]

I have a scripting problem that I can't wrap my head around. At least, I think it is a scripting problem..

Basically, the database matches salespeople with clients. So far, so good. I have tables and portals in place that allow me to relate a table full of clients to a table full of salespeople, such that the clients can have a relationship with more than one salesperson and vice versa. The join field between the salespeople and clients is a 'Contracts' table that allows me to characterize each salesperson as a primary, secondary, or tertiary contact to that client.

The problem is this: I would like to be able to generate 'Assignments' for these salespeople, to pay their clients a call for example. It needs to be flexible enough so that I can first constrain the 'Contracts' I would like to target by their type (primary, secondary, tertiary), and then be able to make individual changes to each assignment if necessary.

In other words, I would like to be able to have my database generate and print an assignment page for every salesperson that I have, starting from a generic task that I assign all of them, but also with the ability to 'fine tune' the task for individual salespeople before I print it all out.

My thought was that for this level of fine tuning I need to make each assignment an individual record. So, if I had 100 salespeople my script would need to make 100 individual records for all of them such that I could go in and tweak each one. Each new batch of records would have a shared common ID so that could keep each group of assignments straight.

My starting point would be the Contracts: what I call the join table between my salespeople and clients. I would like to be able to perform a find on these contracts (for primary ones, for example), then have that found set of records somehow copied over to another table for further manipulation and printing, so that I can do that kind of manipulation and not effect the original 'Contract' between the salespeople and clients.

The only solution I have come up with for this is some kind of script that creates a new ID for a new batch of assignments, loops through my found set of Contracts, copies them all to a new table, sets the value for them all to that new ID, then displays the whole thing in list so that I can insert specific notes to my salespeople here and there before finally printing.

I apologize in advance if this does not make sense the way I have described it. It could be that there is a far easier way to accomplish this, but the only thing I could think of was a looping script to use 'Contracts' as a template for each new assignment. The problem with this approach that I see is that each new assignment would represent dozens if not hundreds of records..

[Ugo DI LUCA]

Welcome to the forums and thanks for the detailled post.

May be as we all did you will find that you've quite answered it when writting it. Because in my opinion this is not a scripting problem.

There would be plenty of solutions, starting from the one you are thinking of. But as you stated, this could lead to a huge number of records when your database evolves.

The difficult part here is to mix specific tasks with some that could be more generic and assigned to everybody. One solution could be to have a multiline key in the Assignments that would allow to store several person Ids ( separated with a carriage return )

This gives the ability for an OR relation. Then a task could be assigned to an individual or a group of person.

HTH

[asfasf]

Thanks for your thoughts, I actually was able to figure it out by looking at it in a few new ways. This Filemaker bug has quite a bite..

Scalability is also less of a problem than I thought. One record per salesperson is a manageable amount for each whole sheet of assignments.

My script automatically takes the found set of salespeople and generates a new record for each one of them in a new table (with one key and the rest matching lookup fields). I transfer each field with variables and loops. So far, so good. Now I am having trouble with the initial search selection criteria.

Right now my script simply creates a new record for every salesperson every time I run the script. I would like a single search form to use that allows me to use multiple fields from multiple tables, and I am not sure how to accomplish this.. with portals somehow? The idea is to have a unified assignment sheet generation page, where I have fields from multiple tables that I can use as generation criteria.

Is the answer to unify the data in these disparate fields into one table that I can then do lookups from? I'm sort of turning in circles on this one.

Also, would this be more appropriate in a new post?

Please let me know what other info I can provide, and I really appreciate the help.

[comment]

I am not aware of any bug here. If you want to "be able to make individual changes to each assignment if necessary", you need an individual record for each assignment. You could start with a group assignment record, then break out each individual assignment when it becomes personalized, or append individual notes in a child table, or just create individual assignment records right from the start.

Either way, the amount of records is given by the amount of information that needs to be recorded. If you have dozens or hundreds of individual assignments, then that's the amount of records it's going to take. Note that a group assignment can reduce the amount of records - but then you cannot easily produce a report of people by assignment, or assignments by people.

BTW, instead of looping, you could create a new batch of assignments by importing the found set of Contracts into the Assignments table.

I don't understand your last question. You can search for records in ONE specific table at a time. Your search criteria can apply to fields in that table and/or in RELATED tables. You can put these fields directly on a layout of the searched table, and enter criteria into them when in Find mode.

I am not sure what lookups have to do with this, or why are lookups even necessary here.