Script to go to a related record in another table

[DreadDamsel]

Hi guys,

I have a Contacts table and an Activities table. A Contact can be a leader of many Activities.

On the Activities Details layout, I want to be able to select a Contact from a dropdown and have a button that can then be used to take the user to the Details page for that contact.

I've tried several things from researching, but can't seem to get it working. PLEASE can someone help me. It's driving me nuts.

I'll also want to do show the Activities a person is leader of on the Contact Details layout, but I'm presuming that's a simple portal.

[siroos12]

What is the relationship structure between the two tables? Is the contact name the relationship key? If yes, is this the only field contributing in the relationship? 

 

And yes, as you mentioned a portal would help you showing up child records on a layout of the parent table records. 

Edited May 30, 2017 by siroos12

[DreadDamsel]

Both Contacts and Activities have ID keys and are related via these. I've also set up relationships so that Activity Leader in the Activities table relates to NameWhole in the Contact one. Don't know if that's right. I have SOOOOO much trouble wrapping my head around Relationships...

[Agnes Riley]

1. You can have a portal on a popover on your activities layout if you don't have FM 13 or higher. In that portal you want to show all the contacts (unless you want to filter them, which I'm not getting into now). That portal needs to come form a table occurrence that's hooked up by something called a "Cartesian join". Which means you'll choose the "x" symbol at the bottom. So every activity record can see every contact record.

2. Then you can select a contact (you'll have to script that process and add a button so you can click it), and that one will be added to your portal of people participating in an activity. Then I recommend having a field as a role, and you can choose "leader" for that person.

3. After that's done, you can have a button next to your freshly added contact and then you can click that button and it should grab the ID of your contact, go to the Contact Detail layout and show that contact. Then will have to be scripted, yet again.

This is not a completely easy task for someone who just started with FileMaker, but it's not impossible. You'll just have to concentrate in crafting the steps one by one.

ALWAYS related records to each other by an ID, never a name.

Also, start reading the FileMaker Training Series. It will shed light on a lot of things. There are also a lot of relationship graph examples on the web. https://www.google.com/search?q=filemaker+ERD&source=lnms&tbm=isch&sa=X&ved=0ahUKEwic2vH67ZbUAhWhjlQKHSbqAOUQ_AUICygC&biw=2636&bih=1284 Start looking at these, too.

Edited May 30, 2017 by Agnes Riley

[comment]

5 hours ago, DreadDamsel said:

On the Activities Details layout, I want to be able to select a Contact from a dropdown and have a button that can then be used to take the user to the Details page for that contact.

I don't really understand your requirement. By your description, each activity already has a related contact - so all you need to go to that contact's record is a simple Go to Related Record step. If you want to select another contact (for viewing only, without modifying the current activity record!), you will need to add a global field to hold the selected contact's ID. Then you can either define another relationship to the Contacts table using the global field as the matchfield opposite ContactID, or go to a layout of Contacts and perform a find for the selected ID.

However, it would be much simpler, IMHO, to go to a list layout of Contacts and select the next contact to view from there. Then you won't need to add any fields or relationships.

 

 

 

Edited May 30, 2017 by comment

[siroos12]

One other solution to relate a parent to a child is using drop down with a value list.

The trick is to set the contact name to lookup values from Contacts table based on the relationship of ID_Contact (From Contact Table) = IDfk_Contact (From Activities Table) fields.

Then put the IDfk_Contact field on the Activities layout and set it as a drop down or popup menu. The value list would look for values from the ID_Contact (From Contact Table) But only shows the values from ContactName field(From Contact Table). 

In the next step, put the ContactName (from Activities Table) on top of the ID_Contact (From Contact Table) same size but locked in browse mode. 

Now if you click on these fields, a drop down or pop up menu would show up with list of contact names. By selecting any contact name from the list, the value list would set that contact's ID in IDfk_Contact (From Activities Table) and lookup the contact name in ContactName (from Activities Table).

 

I hope it is clear. let me know if you need elaborations.

 

I have attached a sample file for you.

 

Regards,

 

Contact Sample.fmp12

[comment]

6 minutes ago, siroos12 said:

The trick is to set the contact name to lookup values from Contacts table

Why would you need this duplication (and possible discrepancy)? Why not simply display the contact's name directly from the contact's record in the Contacts table?

Anyway, I am not sure what problem this solves. I thought OP had this problem solved already.

[siroos12]

1 minute ago, comment said:

Why would you need this duplication (and possible discrepancy)? Why not simply display the contact's name directly from the contact's record in the Contacts table?

Anyway, I am not sure what problem this solves. I thought OP had this problem solved already.

You are right Comment, it is all depends on the business roles. To show up the contact name, we could also use the ContactName field from Contacts table directly instead of storing it in Activities table using lookup. There are cases that clients do want to store the contact name in child record and not change it even if the contact name has been changed. In this case lookup would do the job.

Thanks for the comment 

[DreadDamsel]

For your info and anyone else's reference, here's the setup. I'm working with FM 12 Advanced.

It's a community centre database that keeps a record of all the people we need to have details for - so anyone who hires a room, signs up for an activity, or is associated with the centre in some way.

Anyone who hires a room has to sign an agreement and pay a bond to get an access card for the building, though this may not necessarily be the person running the activity - it may be the secretary of their group, rather than the activity leader. The agreement/bond isn't an issue as it's directly part of the contact's details, as is any membership they may have - a contact can only have 1 of each of these. All the general contact information, membership, agreement and bond stuff is done and works fine.

Where I'm having difficulty is:

Table for: Contacts

Table for: Activities

Table for: Volunteer Projects (once I get my head around the associations for Activities, I'll adapt it for these projects too)

An activity is run by a single contact, but a single contact can be running multiple activities.

I have all contacts on a value list referenced from a calculated name field (joins first and last names - the calculation is in the field which is a text), and I have all activities also on a value list referencing from a field (the Activity Name).

On the Contact Details Layout, I want to be able to select from a dropdown, 1 or more Activities the contact is running and be able to then go directly to the activity details via a button.

Conversely, on the Activity Details Layout, I want to be able to select from a dropdown, the 1 person running the activity and go directly to the contact details via a button.

I have ID_pk fields for each of my Tables, and matching ID_fk fields in them all to. The Contacts Table is my central starting point, as everything revolves around them. 

I'm not new to Filemaker (have played with it since FM3), but I'm not a scripter and my head simply doesn't work that way. If I understand the logic of something I can work it out. I do however, understand the complexities and logic of Excel formulae. I find all discussion relating to Relationships by FM people is from a scripter perspective, and I've not been able to find anything that explains it in plain language. If there's something anyone knows - I'd be only too happy to learn.

I've yet to try the solutions you've all mentioned above, but will get to them later today - I'll let you know how I go - and thank you very much for your assistance with this - it's much appreciated:)

[Agnes Riley]

I wouldn't use drop-downs for this. You are better off using portals. (You just don't know it yet.) Drop-downs are useless for one, and get exhaustive for more than 10 records.

When you assign a contact to an activity you want to stop their ID in the Activity::ID_contact (or fk_contact.) ID_pk is redundant because ID means the primary key. I recommend you cal the primary keys just simply "ID" in the table, and when you reference them in the child table (activity in this case) you call the foreign key ID_table (ID_COntact in your case). So if you just choose a contact from a value list (drop-down) you can't store their ID, you can only store their name (unless you show the second field, but scripting is nicer in my humble opinion).

Sounds like you don't have a problem, rather you need to learn to develop in FM. Hence I recommended the training series.

By the way, the ACTIVITY table comes after the project. So you can call the activity PROJECT_ACTIVITY if that helps. That assumes you can have many activities for one single project. And all those activities will have one leader running the activity. And then you can add other people who are volunteers.

So, again, start simple and build your way up. Learn to add a contact to an activity. Read my post above again. Once you hacked this you can move onto the other tasks. Also, identify your tasks clearly and put them in order of importance.

[siroos12]

Does attached file help? I hope it is simple enough to understand.

Contact Sample_V1.fmp12

[DreadDamsel]

Siroos. Thank you so very very much for this. It's EXACTLY what I needed and works well.

However - as in the way of many of these things, once my user saw it, there came the inevitable query... "Can we list all the participators in a class and have their participation showing in their Contact Details layout?".

So I created a new table called Participants, with participantsID_pk and have linked it to the Contacts table via a participantsID_fk, which I was hoping to use as a Portal on a tab on the Activities layout so I could add multiple participants and have their phone, email and mobile display. Participants is also linked to the Activities table via a ActivityID_pk/fk relationship. The participants I've structured as dropdown selector the same as your example (using the lookups). Portal and lookups all sourced from the Participants table. In that table, I've got Participant_name lookup to ContactWholeName in the Contacts table.

What's happening is that if I link the portal to Contacts table, I can see the fields, but the dropdown doesn't work. If I link it to Participants, the fields don't show. Just want to be able to add participants to classes and on the contact details for each participant, the relevant Activities they're signed up for can be seen. (so we can go to a Details and see what they're doing and have done - so keeping a record of past participation).

Sorry about asking this, but if I can get this sorted out - I can hand the damned thing over. Setting up all the printout pages and stuff is easy peasy.

Oh yeah. Your example I've also adopted for our volunteer project leaders so double thank you:)

Cheers

Lesley

Edited June 20, 2017 by DreadDamsel