Jump to content

Recommended Posts

Auraboros    1

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
comment    1,372
Posted (edited)
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 by comment

Share this post


Link to post
Share on other sites
Auraboros    1

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

Share this post


Link to post
Share on other sites
comment    1,372
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.

  • Like 1

Share this post


Link to post
Share on other sites
Auraboros    1

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
comment    1,372
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.

Share this post


Link to post
Share on other sites
Auraboros    1

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

Share this post


Link to post
Share on other sites
comment    1,372

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.

 

Share this post


Link to post
Share on other sites
Auraboros    1
Posted (edited)

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!

Screen Shot 2017-05-08 at 8.06.56 PM.png

Edited by Auraboros
additional material

Share this post


Link to post
Share on other sites
comment    1,372
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.

 

Share this post


Link to post
Share on other sites
MikeKD    7

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.

Share this post


Link to post
Share on other sites
Auraboros    1

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

Share this post


Link to post
Share on other sites
comment    1,372
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Similar Content

    • By Guy_Smith
      Is there a difference in how Filemaker reads/acts on the two following Relationships:
      1.  Parent Table A --< Join Table AB >-- Parent Table B  and
      2.  Parent Table B --< Join Table BA >-- Parent Table A  ?
      After my last disastrous foray into designing a Relationship Graph, I went back to the Filemaker Training Series to re-re-re-read about building correct ERDs and Relationship Graphs, but only managed to confuse myself even more.  The FTS examples all use the Anchor and Buoy concept, but seem to have a huge amount of unnecessary repetition, unless I'm completely off base.  Using their "04_Bonsai" Relationship Graph as an example, they have one Anchor-Buoy set of
      ORDER --< order_LINEITEM >-- order_lineitem_PRODUCT        and another Anchor-Buoy set of
      PRODUCT --< product_LINEITEM >-- product_lineitem_ORDER.  
      These seem functionally IDENTICAL to me - you can even swap the icons around so the ORDER TO is on the left, the LINEITEM TO is in the middle, and the PRODUCT TO is on the right for both sets without changing the Relationships between them  (and yet a third set with LINEITEM on the left and both lineitem_ORDER and lineitem_PRODUCT to it's right, which can be manipulated to match the above two sets as well).
      If i'm sitting on a record in the ORDER TO, looking through the LINEITEM TO into the PRODUCT TO, does it matter if I have to look left or have to look right?  Or are they just adding a bunch of Anchor-Buoy sets on the Graph to, ummmm, "clarify" process flows?  Are there any performance issues in eliminating the redundant TOs? Is it considered a Best Practice to place a TO on the left of the Graph for every Table and delineate Relationships to the right of each beginning TO?04_Bonsai Relationship Graph.pdf
       I have included a screenshot of the Relationship Graph (squished to fit) to illustrate my problem and, as always, your insights are greatly appreciated.
      Sincerely,
      Guy
    • By Lyinggod
      I am building a digital asset manager in FM 13 for Windows. I am having problems displaying filtered data in a portal. I have series of scripts that bulk imports the file names & folder structure and creates a collapsible hierarchical keyword structure that is comparable to the folder structure. It creates all the joins between keywords and assets. 
      Example:
      C:\Automobiles\Cars\Fiat\Model1.pdf
      results in
      C:
      ---Automobiles
      -----Cars
      ---------Fiat  
      When "Fiat" is clicked, all assets associated with this path are shown in a second "Asset" portal using the filter:
      not IsEmpty ( FilterValues (  Data for Keywords::ID ; global::gCurrent Keyword ) )
      This portion works fine. User added keywords are intended to appear in the same keyword portal using the structure of:
      c: (imported example above)
      Author
      ---A
      ---B
      etc
      Series
      ---A
      ---B
      ....
      ---F
      ------User Added keyword (ie Fiat)
      A script inserts the user created keyword alphabetically into the keyword hierarchy and creates the join table record. This is where the problem occurs. Clicking on the user keyword in the keyword portal returns an empty asset portal. I have double checked all PK values in the asset and keyword tables against the matching join table records and they are correct.  I have also manually deleted and inserted working and no-working join link records to see if there is an issue with how the joins are created (manually vs script) without affect.
      Any suggestions on why this may be occurring would be greatly appreciated.
      Thank you

    • By Lyinggod
      I am very much a novice with FM so any assistance is greatly appreciated. I am using FM 13 to develop a local digital asset manager. I have one portal with a list of keywords connected to a list of assets via a join table. When a keyword (button) is selected, the related ASSETS are displayed in a second portal. On a basic level, this setup works correctly. However, I am trying to expand the ASSETS that are displayed.
      Currently, the ASSET portal is filtered using:
      global::gCurrent Keyword = Data for Keywords::ID
      Given this example 
      1   doc1.pdf
      1   doc2.pdf
      2   doc3.pdf
      2   doc4.pdf
      If I enter "1" or "2" in global::gCurrent Keyword, then the expected records are displayed (1= doc1 & doc2, 2= doc3 & doc4). However if I enter a list containing 1¶2 then nothing is displayed.
      Any suggestions on how this could be resolved would be appreciated.
      Thanks in advance for your assistance.

    • By MSPJ
      Hi - I'm trying to do something that seems like it should be doable, but I can't quite figure out how to do it - I've thought about using relationships and SQL, but neither quite gets me there. 
      I have 4 tables: Visits, VisitClaims, Claims, Account. The relationship between Visits and Claims is many to many, thus the join. 
      When I'm standing on the Claims table , I can easily see all the visits that are assigned to that Claim - simple. 
      If I'm standing on Account, I can see all the visits that have been assigned to claims using that account 
      What I'm trying to do is stand on Claims, and find Visits that have NOT been assigned to claims using the same account as the current claim. 
      In other words, when I go to create a claim, I don't want to see visits that have previously been assigned to claims associated with the same account as the current claim.
      I'm sure I could do this with some kind of multi-step find -- Find the set of visits that were assigned and then omit those from the full set - but I'm not sure how to create a portal filtered by that definition.  I could do a script that runs the find, but I'd have to rerun it every time something changes.  That's a possibility, but I'd prefer not to do it that way.
      It seems like there must be a more straightforward way to do this...
       
      Thanks!
    • By GarthT
      Hi everyone!
      I am a surgeon trying to create a FM solution to analyse some of the cases I have been involved in.
      The main table I have set up is: Patients and one patient can have many "Referrals"; "Consultations"; "Admissions" and "Operations" - I have set up one to many relationships and this all seems to work fine.
      I am working on the relationships between "Operations" and "Surgeons"; "Procedures"; "Antibiotics" and "Complications". I believe these should be many to many relationships; i.e. one operation can have many surgeons and one surgeon can do many operations. Thus I have set up a join table containing the foreign keys for Operation and Surgeon.
      I want to be able to enter surgeon specific data (Name, Grade etc) in the Operation table by looking for it in the Surgeon table.
      I believe this would be best done using a portal.
      Try as I might, I cannot get this to work. The portal just appears as a box in browse mode and I cannot enter it, let alone, look up records from it.
      I have set the portal to allow entry in Browse mode and have set the relationship to allow creations of records via this relationship in the Operation table.
      I am going around in circles and not getting anywhere! I would be grateful for any help.
      I have attached to file
      Thanks
      Garth
      Pilonidal Solution v.2.fmp12
×

Important Information

By using this site, you agree to our Terms of Use.