sbphoto01

Ok, will post a graph tommorow, thanks for your help!

Hello LaRetta, thanks for your response! The contacts table is linked to the email correspondence table through a join table to my Events table, then the email table (where this merge variable 'substitute' function lives). There's no direct relationship between Contacts and Email, so i can see the related Contacts from email but not sure how best to to make this work. If you could explain in simpler terms I'd appreciate!... how should i insert the Contact ID of the primary contact right in my Events table? thankyou for your help. Would creating a direct relationship between contacts and a new instance of the email table make things easier to filter using the relationship?

Hi all,  I have an email template table where users of my solution can create data driven email templates which used to pull data simply from the same table.  My problem now is that the data is in a separate table and there are multiple records to pull data from as merge fields.  I'm pulling data from a contacts table where a few of the related contacts are flagged as 'primary' contacts.  I want to pull in merge variables but get filemaker to identify the primary contact records and pull data from those only, or from the first primary contact in the sort order.  Attached is a screenshot of my merge variable calc using the substitute function to replace the merge text...  At the moment this just pulls in the first or second contact's first and last name field, i need it to look at the related contacts and pull in data from primary contacts only  Does anyone have any idea how you could best do this? Â

Hi All, I have an events search icon with a script which enters find mode and set selection on my event description field... the event description field is set to auto complete with existing records to help find the right event record quicker... but it does this by matching the words at the start only... Is there any way to set it to match any part of the event description? Surely this is an easy one! Any help much appreciated.

The related contact records are in a portal with a sorting script attached which switches the sort numbers... could it be that although they appear in order, the merge variable substitute script is putting them in creation order by default then? As the order I see them in the Contact|Events|Join table (sort field sort order) is certainly not the order it is pulling them in as variables... I'll create some new contact records and refrain from adjusting the sort numbers via my portal script and see if i get a different result. Any other ideas? I'll try to post an example file to show how i have things setup. Thanks for all your help so far and Lee... I've updated my app version details.

Hi I have a sort field (auto enter serial) in the contact table and I sort via the relationship using this field, but when pulling in via this method there doesn't appear to be any order... it's all a bit randon no matter which way I sort the relationship. Should I script a sort order step in the merge calc somehow?

I'm having real problems getting the getNth part of my merge variable script to pull in the correct record... The Email Correspondence table 'sees' the contacts records, but not in the correct order... The relationship is Contacts - Contact|Events|Join - Events - Email Correspondence I thought I had it working bu when i add in a third linked contact to the event it then pulls in the third contact rather than the second. Bit stumped!

Hi, just one other thing, how do I determine the sort order of a table as it's returning the first and second contacts details but not in the order I want them.... I have a sort order auto enter serial in the contacts table which i use to sort and order the portal. I think I've found it... you can do it in the relationship.

Awesome! Thank you so much! that works great.

Hi, Thanks for your reply, and your suggestion, the relationship between email correspondence and contacts table is through the events table and the contacts are related to events via a join table. How would i pull in the second record in the contacts table as a merge variable? Ignore the pattern count find & replace reference... I meant substitute function... I'm using pattern count for another thing and got mixed up :0)

Hi all, apologies if this is the wrong place to post... Here's my problem. I have four tables in my relationship... a 'Contacts' table, an 'Events' table and an 'Email Correspondence' table and an 'email templates' table. In my email templates table, i have merge variables which are replaced in a 'merged view' calc field using a pattern count find & replace, which works great. So for example if <<event_venue>> appears in the template creation field it is replaced by the actual 'event venue address' in the related events table... all good. What i want to be able to do is automatically replace another <<primary_contact_1>> with the first related contact in the contacts table... is this something that's possible? In previous carnations of my solution, all the contacts lived in separate static fields in the events table, so i was able to pull them in easily as merge variables but now there are multiple related contact in a separate table, I'm not sure how to do this. Any help would be much appreciated! thanks, Steven

Hi All, I've a bit of a problem with my solution... I've been doing some major redevelopment on the file, deleting tables, instances etc etc... the solution works fine apart from when I go to delete one or more records from the main events table in my solution... Â Â I get this message - Â Â Any ideas how I can sort this problem? I'm unable to undo deleted tables... Â hope someone can help. Â Steven

I have just discovered my script works as required but only if the drop down is a 'pop up list' and not a 'drop down list'.... why is this?

Hi all, I hope you can help. I have a drop down field in a portal with a value list attached, the value list is a number of different written functions, for example "Set Priority", "Set Normal" , "Delete"... there is a script trigger set to 'OnObjectModify'. The idea is rather than have many different buttons in the portal for these different functions I want the user to select a function from a drop down, and based on their choice, the script trigger carries out the requested task. But everytime I request a task from the dropdown, it always goes to the first record in the portal, despite the 'Go to Related Record, Match Current Record only' setting being turned on. Below image link shows my script trigger on the portal dropdown... so you can see the first If statement checks to see if the field 'action_select' (the drop down field) is set to "Set Primary"if so it sets another field in the same table to a 'Yes' value and resets the dropdown field to "Action >" but this is'nt happening as it doesn't go to the correct record. The flush Cache and refresh window steps are to ensure the portal refeshes properly when we come back to the original layout, so you can see the changes.

Hi guys, I've used the list function to gather mobile numbers from a related contact table foundset, but when I ask that it places them in a text field in another table, it only pastes the first record. Any ideas why this is?

Thanks Guys, that was a great help! just what I needed!

Hi all, I have multiple table instances in my solution, for various reasons... some are search or filtering to a portal in a different table, based on a global search field, others are connected to other tables for pulling data into those tables... My Question is... having all these seperate instances of the same table seems a messy and convoluted way forward... is there a way around this? And before anyone mentions it, I know I can create portal filters for searching and filtering related tables. Am I just missing something critical?? Steve

Hi all, I'm unsure this is the right place to ask this question but here goes... In my solution I have an Events table which is link via a join table to a contacts table as it's a man to many relationship, so I have the Contact Fk and the Events FK in the join table to make the relatonship. Previously I had restricted the contact info to just two contacts per event, so those contact fields (name, Mobile No) were in the events table, this made it easy to concatenate the mobile numbers to place in a 'Send sms' URL string... which worked perfectly... Now I have the contact data in a seperate related table, with individual records for each contact, and I am stumped as to how I can concatenate the mobile numbers to create my send sms url string again. I know how to create a foundset, of the related contacts, but not sure how to collate data (in a global text field for example) from individual fields in multiple records. Can anyone help?? All suggestions much apreciated.

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

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

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!!

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

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

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

Hello, I have integrated the email plugin with my solution and the email fetching was working great, but now I don't seem to be able to fetch my most recent emails... I am accessing a gmail account via imap and the message browse portal is definitely set to date order descending (newest first) Any ideas anyone? Also what's the significance of telling the fetch script how many messages to fetch? as it now appears to have fetched more messages than there are in my inbox, according to gmail's count of the messages in my inbox.