mass linked records to one event

[Auraboros]

Hey...

Here is the scenario...

Contact table joined to Contact|Event table joined to Event table

Portal in Contact table of Event table to see all Events linked to a particular Contact

Needing to link 100 (or any number) of Contacts to 1 Event in Event table

Do not want to do this manually. 

I have all the tables and relationships set up and everything works. All I need is a way to create multiple records (joint records in Contact|Event table) in multiple Contact records so I don't have to enter them in one by one...which of course I can do.

I figure this would be a script...but I don't even know where to call the script...in the Contact record? In the portal row? in the Event record? Where does the script button go? And what is the script? Once I create a found set in Contacts, how do I assign one Event to many contacts (in the found set) without having to enter the link manually.

THANKS!!!!!

Todd

[Steve Martino]

One way would be to put all the contacts on a modal/picker window.  Your script would grab the primary key of the event, open the picker window and pause.  On the picker window, you would check off the contacts you wish to add (via a checkbox next to each name).  A button (Add contacts) would resume the script which could either loop thru the records and add the event primary key to the join table's foreign key, or use Replace field content.  Close picker window.

[comment]

57 minutes ago, Auraboros said:

Where does the script button go?

Wherever is most convenient for you.

 

57 minutes ago, Auraboros said:

Once I create a found set in Contacts, how do I assign one Event to many contacts (in the found set)

Creating a found set in Contacts is not enough. You also need to select the event.  I am not sure how and in what order you want to do all this. Once we understand your intended workflow, the script will fall in place.

 

Edited May 8, 2017 by comment

[Auraboros]

Thanks! I am trying to avoid checking 900 or more names to add the event so I don't think a "picker window"
 would work...but it is a cool idea!

The work flow isn't crucial. Off the top of my head this is how I would intuitively do it:

1. Go to Contact table and create found set through a "Find" based on criteria (selecting the contacts that the event--in this case a mass emailing--went to)

2. Go to Portal on the first record of found set and add the event.

3. Click a button somewhere (in the portal row?) and assign that same event to the rest of the found set.

This might not be the best way to do this. I see there being confusion as to which event to "assign"...I know that the "Assignment" is actually creating records in the JOIN table between Contacts and Events. 

I don't know FM well enough to think this through, but is it possible to have a script that uses the found set of another table? It might make more sense to do this...

1. Go to Contact table and create found set.

2. Go to Event table and find record of event you want to assign and press a button that says "Assign Event to Contact Found Set"

3. Have a dialogue box that comes up saying "You are about to assign this event to 900 Contacts" (or whatever the found set in Contacts is) Then click ok and the script creates JOIN records for 900 contacts.

This would be the preferred way to do this...but I don't know if a script can utilize a found set in another table...can it?

THANKS

Todd

[comment]

29 minutes ago, Auraboros said:

It might make more sense to do this...

1. Go to Contact table and create found set.

2. Go to Event table and find record of event you want to assign and press a button that says "Assign Event to Contact Found Set"

3. Have a dialogue box that comes up saying "You are about to assign this event to 900 Contacts" (or whatever the found set in Contacts is) Then click ok and the script creates JOIN records for 900 contacts.

Yes, it's perfectly possible. In fact, there are two ways you could implement this; one is to loop over the found set in Contacts and create a new join record for each, say (roughly):

// REMEMBER THE SELECTED EVENT
Set Variable [$eventID; Events::EventID ]
// RETURN TO THE FOUND SET IN CONTACTS
Go to Layout [ Contacts ]
Show Custom Dialog [ ... ] 
  If [ Get ( LastMessageChoice ) = 1 ]
  Go to Record [ First ]
  // FOR EACH CONTACT ...
  Loop
    Set Variable [ $contactID; Contacts::ContactID ]
    // ... CREATE A NEW JOIN RECORD
    Go to Layout [ EventContacts) ]
    New Record/Request
    Set Field [ EventContacts::EventID; $eventID ]
    Set Field [ EventContacts::ContactID; $contactID ]
    // RETURN TO THE FOUND SET IN CONTACTS
    Go to Layout [ Contacts ]
    Go to Record [ Next; Exit after last ]
  End Loop 
  Commit Records[]
End If

The other option is to import the found set in Contacts into the join table (mapping ContactID to ContactID), then replace the contents of the EventID field of the imported records with the $eventID value.

[Auraboros]

You guys blow me away...do you all have IQs of 200+? Amazing.  I have been messing around with FM for a while now and although I can come up with some pretty cool stuff, I get totally stumped with the logic in these sorts of scripts. 

Thank you so much Comment...I think I follow this. I am assuming that you first create the found set in Contacts in this scenario? The script is run from a button in the Event record I want to link to my Contacts found set, right? Like in my second scenario? If I don't first have a found set, then it will try to attach the event to my entire contacts database (if "SHOW ALL" is clicked)...the "Show Custom Dialogue" is where I say "You are about to link 900 Contacts with this event, proceed?" Correct?

Thanks again...I'll give it a whirl...I'll be back if I flub it up!!!

Todd

[Steve Martino]

Don't forget to work off of a backup copy (or two) until you get it all sorted out and working properly.

[comment]

29 minutes ago, Auraboros said:

I am assuming that you first create the found set in Contacts in this scenario? The script is run from a button in the Event record I want to link to my Contacts found set, right? Like in my second scenario?

Yes. 

Also keep in mind that this adds the found contacts to the selected event. It does not check if any contacts are already assigned to the event, and it does not check for duplicates.

[Auraboros]

It works!! Yes, it would be nice to find duplicates and such...I'll have to work on that...

NOW...I do have a problem. Of course my situation is more complicated than what I originally presented. Not only do I have a table of contacts to join to a table of events...but I have two other tables I want to join to the same event...

Let me simplify...as I stated above...

Contact A table mass link to an event in Event table. Using Join Contact A | Event....Your solution worked beautifully!

Now, add Contact B table mass link to the same event in Event table.

I set up the same script, with a button that says "Link this Event to Found Set in Contact B"

I changed all the parameters in the script to now make the records in a new Join Table ContactB|Event.

I made a new Join Table. Related it to Event, just as before, but when I tried to relate it to Contact B it said I couldn't do that and forced me to make Contact B 2...a table copy.

The script works...EXCEPT...the portal in Contact B that is related to the Join Contact B | Event shows ALL of the records...NOT the related record only. Everything looks exactly the same as the Contact A script, portal, etc. which works great. The Join table shows all the correct IDs put in all the correct fields. But Contact B portal shows ALL of the records in the Join Table (for Contact B and Event) not just the one associated with the Contact B ID.So if I do a found set in Contact B of 20 people, and run the script, all 20 records (from the Join table) are included in each contacts portal.

I think the problem is with the copy of the Contact B table. I changed things to reflect the #2 table wherever I could, but that didn't work either. Is there a solution to this?

Thanks!!

Todd

[comment]

I am afraid I got lost along the way ...

First of all, why do you need to have two Contacts tables? That doesn't sound right. 

Next, if you get an error message, post it verbatim. I am guessing (!) you already have a relationship (direct or indirect) between Events and Contacts B. So Filemaker is telling you need to use another occurrence of the Contacts B table (not another table, and not a copy) to create the additional relationship. Or another occurrence of the Events table. 

Note that the relationship will only work between the table occurrences that it uses. If your layout and/or your portal use other TOs of the same tables, the result will not be what you expect.

 

[Auraboros]

We track a variet of contacts...orchestras, staff and conductors. They all relate to one another (an orchestra can have many conductors, and a conductor can work for many orchestras.) However, we send the same marketing material to all contacts (or selected ones within the groups). Contract A in the example I gave is the orchestra table, Contact B are conductors. I have one event table...1 orchestra table and one conductor table. I want to link 1 event to records in the orchestra table and records in the conductor table. I know I have to do this in two separate actions with two scripts and two join tables. The relationship between the orchestras, join table, and event works fine, but when I try to do the same thing with conductors, a new join table, and the same event table, it doesn't work. It made me create another occurrence of the conductor table before it would let me relate the join table to conductors. 

The weird thing (to me) is that it all seems to work perfectly fine...the join records are created properly...but the portal in Contacts B shows ALL the join records...not just the ones related to that contact ID...why is that?

I know you can't tell much from a screen shot...but there it is. I can send the file too but I don't know if anyone wants to dig into that!

Edited May 9, 2017 by Auraboros
additional material

[comment]

7 hours ago, Auraboros said:

However, we send the same marketing material to all contacts

To me, that alone would be a sufficient reason to keep all of them in the same table.

 

7 hours ago, Auraboros said:

the portal in Contacts B shows ALL the join records.

I can only guess, as I don't see Contacts B (or, for that matter, Events) in your RG. But I think I can say for certain that a portal to Marketing, placed on a layout of Conductors, will not work as expected. You must place it on a layout showing records from Conductors 2. Or define another relationship, using new TOs of Marketing and the join table, linked to the Conductors TO - and then use a portal to these new TOs. That's what I was trying to say in the last paragraph of my previous post.

 

[MikeKD]

Aren't the "orchestra" and "contacts" different?

Shouldn't the event be attached to contacts within the orchestra? (.e.g secretary, chairman, bass trombone etc)

And these contacts should be joined to the orchestra - possibly to more than one orchestra.

[Auraboros]

Thanks...Sorry about the RG...I have been using different names (such as Contacts B for clarity in describing my situation, the RG is from the actual database.) Yes, this whole structure may be the cause for these difficulties...I originally wanted staff to be part of the orchestra table, but they are independent, move around, have all different information and often times get marketed to differently. It probably would not have been a problem to have staff part of the orchestra table...but conductors definitely need their own table. They are very independent, some work for several orchestras, orchestras have many different free lance conductors. However, marketing often goes to orchestras and conductors...the SAME marketing event...such as a mailed postal card or email campaign...will be sent to the general address of the orchestra, as well as to the address (usually home) of a conductor...sometimes a conductor that has no orchestra affiliation. The events are the same..the same card or email, so I want just one Marketing table. An event (a single record) I want linked to many orchestras and/or many conductors. I have a join table between orchestras and marketing, and a different join table between conductors and the SAME marketing table. For reasons beyond my FM acumen, the orchestra/join/marketing system works (using the script Comment so kindly wrote for me). The same modified script does not work with conductors/join/marketing. FM required that I make a TO for Conductors...to relate to the join table. The script DOES work actually...all the join table records are properly created, with all the proper IDs in the proper fields. What doesn't work is the portal in Conductors...it shows ALL the join records, not just the one that is assigned to that conductor's record ID. 

Obviously I don't properly understand TO's. 

Thanks!

Todd

[comment]

10 hours ago, Auraboros said:

Obviously I don't properly understand TO's. 

It can be tricky. In fact, many developers have sort of given up on this and gone back to using version 6 methodology to define relationships - look up the so-called anchor-buoy method. Basically they replicate every relationship for every base table in their solution.