Assigning a single contact to multiple events

[sbphoto01]

Hello, I'm fairly new to FM and I am using Pro Advanced 12...

 

I have a contacts table and an events table, with PK's for records in each... i want to be able to assign multiple event records to any one contact. How do I do this? I think i require a cross referencing table to store records of PK's in each table, but I'm not quite sure how the relationship should look.

 

I'd appreciate any help you can give,

 

PS. Sincerely apologies if I have posted this in the wrong place!!

 

Steven

[doughemi]

You need a join table, in this many-to-many situation.  Each join table record contains one event for one contact

Events::PK  = Join::EventsFK
              Join::ContactsFK  =  Contacts::PK

Then, you can make a portal to Events in a Contacts layout, and show all the events related to that contact.

[sbphoto01]

Thanks for your reply Doughemi! 

 

Sorry to be dumb, so the join table need 2 fields contact pk and event pk?

 

So each time I want to assign a contact to an event I create a script to add a new record of the pk's from each table, into the join table. Is this right? 

 

This probably sounds like a complete newbie request to most but this is not a function I have required in my solution previously. 

 

Many thanks in advance!

Steven

[doughemi]

So the join table need 2 fields contact pk and event pk?

 

 

 

Not quite. the fields should be identified as contact_fk and event_fk (foreign keys), but you will set them to the appropriate pk values.  You can add whatever other fields are needed to contain data having to do with that specific contact at that specific event.

So each time I want to assign a contact to an event I create a script to add a new record of the pk's from each table, into the join table. Is this right? 

 

 

 

 

Yup!

 

See http://help.filemaker.com/app/answers/detail/a_id/9922/~/understanding-and-creating-many-to-many-relationships-in-filemaker-pro for more details, as well as do a search for "join table" for more examples.

[Fenton]

doughemi's picture shows the relationship graph's table occurrence. You could name the join table something like "Contact|Events" (yes, it looks odd, but does show that it is a join table between them; a Contact being the "parent").

 

This portal, based on the relationship, you likely want to be sorted by Contacts::Date_created* field, descending (newest on top). I think I would add that sort to the relationship itself, as the "latest" event is most useful.

 

The way I'd add a "row" to such a portal would be via a script. 

 

Freeze Window (not critical, but makes things smooth)

set the Contact's PK into a Variable ("$contact_id")

go to a Contact|Events layout

new record

set its contact PK from "$contact_id"

Commit (no dialog)

go to layout (original layout)

go to portal's field, Event's PK

 

Because of the sort, that new row (record) should be at the top, and the script should just end up in it. [ If not, you'd need to go to it; but I think it would be OK as is.]

 

* This "Date_created" must use the option to set itself to the current date. If (not likely) a Contact can (every) go to TWO Events on the same date, then use a TimeStamp field instead.

 

P.S. You could do this all within the portal itself. But I seldom do that, mostly because the script and field names are not as easy to read; but that's just my personal choice.

[ I know that doing it within the portal is "safer" (for some scripts, but this one is OK) if you're going to use a "FileMaker Go." Maybe someone else can write one.]

 

I hope the above reads OK, and works. To those who know me, I used to write better. However a stroke two years ago whacked many words and their use. I still understand them (mostly), but they cannot be relied on. [ I can actually write much better than I can read; which makes then even less reliable.] It's all just a hobby to me now :-]

[sbphoto01]

Thanks very much Doughemi! And hello Fenton, thanks for your input too, most helpful, i have this working now... 

 

What would you say is the most effective and intuitive way to add / assign Contacts to the Event using a script? 

 

I have one other question, if I want to associate or relate contacts to each other, do I do this via the same method? and view 'related contacts' using a second instance of the contacts table to create a portal?

 

Also if I want to delete a contact from an event, I assume I would need to script deletion of the join record, whats the best way to do this from the contacts portal within an event showing join table records?... is it as simple as performing a find of the event pk and the contact pk within the join table and deleting? Will there only ever be once instance of a join record? my contact & event pk's should always be unique, so this shouldn't cause an issue, am I right? 

 

Sorry for all the questions. 

 

Steve

[Fenton]

For me (and beginners), the simplest way to think about some things, especially ones that clearly require IDs (more than one), is to think about how would link together those IDs; also how many?, as in "one-to-one" or "one-to-many".

 

In this case, you "could" use the Contacts table itself. By creating a field, and putting multiple IDs into it, as a list. That would work. But, as you can quickly see, it is more trouble that it's worth.

 

So I would say, create a new table ( I'd name it something like "Contact|Links"). It will be a "join" type of table, similar to the one you were working on earlier. It is a little more confusing, because it will have two "contact ID"s (and both will have data of Contact IDs, from the Contacts table). However, each is slightly different. I would name the first, as "Contact_parent", and the second as "Contact_child".

 

There will (often) be many records in this which have the SAME Contact ID, in the "Contact_parent" field; as a particular Contact many have several people who are "linked" to it. And may also be many records with the same Contact ID in the "Contact_child" field. What these should NOT be are "dublicates", i.e., records with the SAME "parent ID" & "child ID" combination (with them in that order). 

 

I will give an example (as it is a little confusing). In the "Contact|Links", I'll create this for one particular Contact:

New Record

Contact_parent = 1

Contact_child = 10

Now, in this case, the person who is Contact 10 ALSO thinks of Contact = 1 to be HIS "link", so:

New Record

Contact_parent = 10

Contact_child = 1

(usually so, however if did not want to see Contact 1 from the person who is Contact 10, you would not need that 2nd record)

 

The relationship is build from the Contacts table's ID -> Contact|Links::ContactID_parent

 

You would (or could) create a new Contacts table occurrence (relationship). It will go form the Contact|Links table occurrence "out" (it will NOT be linked back to Contacts). The relationship would be: 

Contact|Links::ContactID_child -> this new one's ContactID (name of it in the Contacts table)

This will only be used to see the "name" of the Contacts record, for the "child" who is in the Contacts table's layout portal.

 

When you add a new "link" to a Contact, you will likely want the two "links" about. I would likely do it via a script, similar to the own I wrote previously (as the portal will likely be sorted by "last" and "first" names, so you can see them well).

 

So, after the script, you'd end up in the new portal row, in the "ContactID_child" field; you'd need a Value List of people IDs to choose. 

 

Oops! You need 2 records, and would only see ONE of them. A way to solve this is to go ahead and enter the "ContactID_child" (using the VL above). Then have a button in the portal to make that 2nd record needed (for that "child person's ContactID", using Set Variable to hold it, while you go back and create it. [You will not SEE this new record, until you go to that other person's Contact record. Yes, it all seems odd; but things like this are odd.]*

 

Another method, perhaps less work, would be to create a global field, and choose that "child person" first. Then click a button on the regular area above/or/left of the portal. It would then KNOW what that 2nd ID is, and could easly run the script as I wrote above, i.e., create 2 "link" records, filling each with both the IDs needed.

 

* I have found that trying to link two tables which are somewhat "similar" is more complex (mentally) than two which are quite different. The names and the ways of using them can get a little "mixed up." Be very clear with your "names" or you will go in circles. For people like me (some brain damage), it is critical to name things clearly; and even then… I cannot stand to even listen to all the above (as it is too hard for me to read it all); so hope for the best. I think it's OK :-|

[bruceR]

Steven: what is the nature of the connectedness? Employees in same company? Working on same project? Completely random user-generated connections? Same middle initial? Same favorite beer?

[Fenton]

Just a quick post, re: Deleting the Contact|Event join table's record, the simplest way is to just but a button in that portal to do so; you do not have to use a script, unless you have more going on. There is one thing; you must use the Script Set, 'Delete Portal Row", i.e., NOT "Delete Record".

 

You could set the relationship, in the Relationships graph, to Delete also, so it will delete the join record if you Delete the Contact record. But SURE that only the join table, Contact|Event as Delete turned on. Best to "save a backup" of your file before testing "delete" things!

 

If you are wanting to actually "remove" a Event, which you'd then want to delete all its join table records (based on its ID to it), then you can make this happen by doing the same as above in its relationship to its relationship to the join table, based on Event ID. Similar warning; make sure only the join table has Delete turned on.

 

In sort either of the "parent" tables can have this "automatic" ability, if the parent itself is deleted. But that is not about the portal, wanted to just delete on row.

 

It is kind of late, and I'm making less sense than usual, which is not great at best. On other thing though :-/, the above is about that first join table; it is not about the later one ( Contact links to each other). Those will likely need a script, especially if two records are created, handling the two IDs, but "oppositely, parent vs child." Of course you would need to know whether you wanted to delete both; likely. [ Connections between "people" are sometimes a little questionable. But I think usually delete both, as both people are no longer wanting to be linked to each other, if either one wants to.]

[sbphoto01]

Wow Fenton, you are amazing, I am able to read and understand exactly what you are explaining... which is pretty amazing given some of the responses I've struggled to understand in other FM forums previously! Thank you so much for taking the time and effort to explain and write all that out for me, it's such a great help. 

 

BruceR... I am modifying an event management solution with a contacts table where many of the contacts are linked to each other by the events, so it makes sense to be able to see a contact's related or 'linked' contacts from the contacts table as well as the events to which any one contact are linked too. 

 

Fenton, If I may ask one other question... I've attached a screenshot of my script for creating the join records in my 'event|contacts|join' table which seems to be working great, this script below is part 2 of the same script, part 1 is simply a global set variable of the event PK, and then it opens the contacts portal layout in a separate window, where i can select which contacts to add to the event, using this script on a button in the portal row of each contact. Does this look ok to you?...

 

 

What I'd like to know is if the above method is the best and only option to do this? I cant help thinking a dropdown within the contacts portal or on the events layout somewhere, with a value list to contact names with a script trigger of some sort might also work as a quicker 'work from one layout' option, but i suppose this would then mean I couldn't add completely new contacts on the fly before adding them to an event. anyway, i'm babbling on!

 

Thanks for all your help so far! :0)

 

 

I setup the simple 'delete portal row' with relationship deletion set to only delete in the events|contacts|join table... thanks for that tip!!... sometimes the simplest things just dont occur to you do they!!

[sbphoto01]

Hi Fenton, maybe I've misinterpreted your join table record deletion using delete portal row function... as it is deleting the contact from the contacts table altogether which is not the desired effect unfortunately. Any ideas? 

 

When you said to put a delete portal row script button on the portal, you meant a portal to the event|contact|Join table right?.... I had put it on the contacts table where i am viewing linked contacts...

 

I'll have to script deletion of the link relationship in the join table from the contacts portal using a reverse of the linking script. 

 

 

Steven

[Fenton]

Yes, the Delete Portal Row function should be in the portal row. It is used ONLY in portal rows (which is why FileMaker gave it that name).

 

Regarding your script. The "contact's event" script I wrote originally was for a Contacts table form view layout; creating a "contact|event" new record for that contact.

 

It seems that your script is for running from an Events table form view layout, then creating a "contact|event" new record for that event.

 

The thing is, you would likely need both. The Events one would be used first. But, imagine this: A contact (who you already have, as he/she went to a previous event) contacts you, as he/she wants to go to a new Event. So it's a secondary use; and you've got their "my events" portal there anyway, so another "add event" button would just be an addition.

 

So ( I also babble on; but with purpose, I like to think :-] ), regarding your script. It looks OK, but a few small points, and one question.

 

It starts with a Go to Related Record [ From table: "Contacts 2"; Using layout: "Contacts" (Contacts) ]

Why "Contacts 2"? If you're on a Events layout, why/how are you using a GTRR to go to the Contacts layout? Wouldn't you just use Go to Layout [ "contacts" form view layout ]?

[And I do not like these " 2" words, as it tells so little.] 

 

Now the small stuff.

 

Why use "$$event_pk"? You only need "$$" if you're coming from a different script; but you said these "two parts" are within the same script. And, this is a separate "sub-script", why? [ I would only use one script for such a simple script [ unless something required it (an seldom does these days); or the main script was so very long as sub-scripts made sence]. So, why not just "$event_pk"? (unless I'm missing something).

 

You only need the second (last) Commit. 

[ Rarely you may want two, but only for odd specific reasons (such as you really need a local Lookup to occur, before another "Set Field" step).]

[Fenton]

Regarding my posting about the whole "Contact|Links" table, with its "contact_pk_parent" and "contact_fk_child". Especially since you answered BruceR's questions. [He always comes up with very good questions, and/or answers.]

 

Since the "connections" you want to see "between the contacts who both/many went to the same event(s)," I think there is a big question about whether you actually require another table for that. Since you could see them by looking from a Contact, thru his Events, then to all the other contacts of that event. Yes, that would possibly show "duplicates" of some people (if another goes to more than one, and same, events of the contact). But those could be made "unique", using a Custom Function (CF) on a List ( the above contacts ), in a calculation.

 

But those would only be visible, not necessarily very useful for much more. There is however a (very interest, yet a bit "high end") method, created by BruceR, which can do and show more. It is called "Virtual List", which can allow you to use a special, though simple, table to let you see lists. Perhaps he could say whether he thinks it would be a good solution for viewing a contact's "contacts of events". 

 

He also wrote the CFs I use for a "unique" ["UniqueListFast"]. The CF I use for sort is named "QuickSort", by Jeremy Bante. Those are for field calculations however; I don't know how to do these with the "Virtual List".

 

On the other hand, you could just use the "new table, with records for each "unique combos of contacts" of events. It would need a new record between each contact to every other (actually two for each combo, for "parent" and "child"). But you would NOT need a new one if it already exists, as those two people were already both at an event previously. It would be a lot of records. For example; your first (ever) event had 10 people. The first person would have 9 records (he does not need one as both "parent" and "child"), with him as "parent" in all 9, and one of the others as "child". Each of the 10 people would get 9. Altogether that's 90 records for that one Event. 

 

The number would go down for each, if there are several people going to multiple events, as each combo must be unique, and must be tested for. This could be easily done with a (new) relationship, a self-based on Contact|Event to itself. Create a calculation field, Contact_pk_parent & "_" & Contact_fk_child", text. That is the "unique combo" to test for. I would also have automated ID (number) for that table. Add that to the relationship. Then, only if: IsEmpty (that relationship::any field)*, then create a new record.

 

This stuff could be done either when each person joins the event. But really, for simple reasons, it is easiest done AFTER an event occurs; all is known, all can be done at once.

 

Basically, this "contacts of events links" is kind of confusing bunch of either records or calculations. 

 

* I'd also create a calculation field, number, result: 1

I'd use that for both IsEmpty or "count" things; simple, fast, and easy to read.

 

P.S. I know some of this is likely of little use, and even somewhat confusting. But I'm just saying what I (kind of) know. Also, as you may have guest, I am retired, and often have time on my hands; more time than brain, it often seems.

[sbphoto01]

Thanks you so much everyone, especially Fenton for your help with this. Cant thank you enough.

 

Steve