Remove From A Returm Seperated List

[Ron Cates]

Good Morning All,

I have a field that holds a return seperated list of keys. Can anyone tell me how to go through a found set and remove a selected item from that list on each record.

Thanks

[bcooney]

Not too sure what you mean. Can you describe in more detail?

[Ron Cates]

I will try. I have a table called Leads with a field that contains a returned seperated list of jobs that each Lead is assigned to. Lets say Job1, Job2 and Job3. I would like to be able to unassign by selecting Job2 for example, from a dropdown and having a script that will find all records assigned to Job2 and remove Job2 from the return seperated list leaving Job1 and Job3 on the list.

[bcooney]

I would suggest not using a multi-key, but instead create a new table that contains the combinations of Leads and Jobs. This table is referred to as a join table. It would contain LeadID and JobID.

Then, from a Lead form create a portal to the new join table, "LeadJob," using the relationship _kP_LeadID=_kF_LeadID. Deleting an assignment will be as simple as deleting a portal row.

Search the forum for more info on join tables.

[Ron Cates]

Actually I have it set up exactly as you described. The reason for the return seperated list is that I have scripted the creation of the join tables. Which requires each lead to be tagged with whatever job or jobs it applies to. The solution does work well. But during developmental testing some groups of leads were tagged incorrectly and I would like to be able to clean them up. Also, I would like to be able to reverse the process in case of future errors.

My thoughts, drawing on prior experience with other languages, would be to evaluate the contents of the field and replace "Job2" with "". However, so far I haven't seen any way to do this in FMP. Am I on the wrong track?

[comment]

This is somewhat confusing, because the very reason for having a join table is to avoid "each lead to be tagged with whatever job or jobs it applies to".

In any case, you can remove the value using the Substitute() function - see:

http://fmforums.com/forum/showtopic.php?tid/191281/

[bcooney]

I believe that you are fundamentally misunderstanding how to use a join table. With join tables there are NO multi-keys.

[Ron Cates]

The return seperated field isn't being used as the join key. If you have some time I'll try to explain the best I can.

The company is a calendar company. We do fundraising calendars for Sheriff and Police departments. For each calendar we sell advertising to raise money. We aquire leads from an outside source for each job according to the county or city. When we start a new job, I download the leads for that area and import them into the database (anywhere from a few hundred to 20,000 or more). On import the name of the job is entered into a field so a script can go in later and find them according to job and group them into sets of 100 for the salesmen to sign out. I have a table called sets and one called leads. In between is a join table that assigns each lead to a certain set of 100. The join table also contains the invoice number if a sale is generated from it.

Each lead can apply to more than one job. For instance if we do the Sheriff of one county and the Police department of a city within that county the leads from the city would also apply to the county. So those leads would be in multiple sets.

Also to make things really fun. For the next year the database needs to pull the leads that resulted in sales and group them into sets of repeat advertisers and regroup the rest of the leads back into sets of 100 to be assigned to a salesperson again.

So here is how my solution works.

I import the leads and set the job field to whatever job I am importing for. Everytime we start a job I will be importing the leads. Even if they are in there from the previous year. In which case they will simply be updated. If a lead is already assigned to another job the new job is added to the job field with a return.

After importing the leads I run a script that finds for the job, loops through and ctreates the set records and joins 100 leads to each with new records in the join table.

So the return seperated list is a list of jobs the lead was imported for. It is not used in a relationship or in any calculation. It just tags the lead with whatever jobs it applies to.

If you are able to understand my explaination and have any suggestions for a better solution I am all ears. I have been struggling with this task for close to two months and although it works the way I put it together it is complicated, cumbersom and slow. I hesitated to post for help because it is so complicated to explain.

If nothing else I still need to be able to remove a job from that list.

[Ron Cates]

Thanks Comment, the Substitute() function is exactly what I was looking for. Is there a way to restrict the substitution to one occurance? I ended up with some duplicate data in the list.

On another tangent, how would I check to see if a job is already in the list? Would If Leads::job = Build sets::xjob return true iof there are more than one job in the list?

Edited May 27, 2009 by Guest

[comment]

Is there a way to restrict the substitution to one occurance? I ended up with some duplicate data in the list.

Would you WANT to remove only one occurrence? What would that accomplish? I must say I didn't understand your last explanation, and I am still puzzled why do you need the return-separated field, when you have a join table.

how would I check to see if a job is already in the list?

IsEmpty ( FilterValues ( item ; listOfValues )

returns true when the item is NOT in the listOfValues.

[Ron Cates]

Thanks for the IsEmpty() tip Comment.

By limiting the substutute function I am just trying to clean the duplicates out with a temporary script. Afterwards I will write in validation into my script to prevent further duplicates.

As for the reason for the return seperated list. It is set on import and allows the imported leads to be found for that job in order to have a script create the sets and join tables. I think that's the simplest explaination anyway.

I know my explaination is confusing. I wish I could explain it better because I really need some help on this monster. At this point I am brain storming on possibly reworking the solution using a seperate table with records to store the IDs of the imported leads according to job. I need to learn a lot more about how to manipulate the records using those IDs once I have them though.

[comment]

OK, I think I understand this a little better now.

The easy way to de-dupe your list is to run it against a full list of Jobs with no duplicates. You can obtain such list either by using the List() function over a relationship to the Jobs table using the x relational operator, or by defining a value list of Jobs and using the ValueItems() function to retrieve it.

Then you do simply:

FilterValues ( full list ; list containing duplicates )

BTW, if you are scripting the creation of join records from the field values, then duplicates shouldn't matter because your script can check the existence of a join record before creating one, e.g.

...

If [ IsEmpty ( FilterValues ( $jobID ; List ( JoinTable::JobID ) ) ) ]

# create a new join record

End If

...

[Ron Cates]

Thanks once again Comment. Got the dupes cleared out and added in the validation. Works Great. Couldn't have done it without you guys!!

Still needs reworking but this will keep it functioning in the meen time.