Portal issue

[miltont]

Hi everyone,

I have created a table that holds 3 fields, -  id, email subject and email text.

These will be used as templates for emails.

The idea is that the user creates the templates ( I can create new records) and then whilst on the Owners layout a portal will show these templates (the same list of templates for each Owner).

How do I create the relationship between the OwnerEmailTemplate table and the Owner Table so this will display correctly?

 

Thanks in advance.

 

[rwoods]

Hello there

I think the question you are asking is 'How do I create a relationship that shows all email templates to all people' i.e. the list of email templates to show is not filtered or restricted in any way based on who the user is.

The answer is to use the 'Cartesian join'. That is a relationship that always matches. Please see enclosed screenshot of a sample relationship graph.

You need to think about how you design the database after that. Presumably you will have a button in your portal called something like 'Select' allowing owners to pick a template from which they would like to start a new email or campaign. You'll need a join table that allows you to create an occurrence of an owner using a template, which will then go on to have all the information associated with that email campaign in that join table. Feel free to come back to us if that makes no sense!

[comment]

1 hour ago, rwoods said:

You'll need a join table that allows you to create an occurrence of an owner using a template,

 I suspect a novice would be even more confused than I am.

[bcooney]

I don't see the need of a join table, either. Owner selects email template and a new "email" record (in whatever table that is, "Interactions"?) is created, setting email subject and body to the values in the template selected.

[miltont]

Thanks rwoods,

I altered the join to the Cartesian join and the records are now showing in the Template portal correctly as shown below with the email subject field on the left and the email body field on the right.

On another layout I have 2 portals, the one on the left is the Template portal (as above, but only showing the Subject field) and on the right is the Email portal where all emails are stored, sorted by date.

When the user clicks on a subject field in the Template portal on the left, (shown as the red arrow) a script runs that:

creates a new record in the Email portal

adds the date, time, the subject and body of the email, and goes to the Author field,

but it doesn't matter which record on the left is clicked, the same subject and the same body is being inserted into the right hand portal.

I have included my script below. Any ideas as to where I have gone wrong?

Thanks in advance.....

 

Milton.

 

 

 

 

[rwoods]

Hi miltont

The issue is that you are losing the context of which line was clicked in the OwnerEmailTemplates portal when you perform the 'Go to Object' &  'Go to Portal row' lines at the top of your script. When you then refer back to the left-hand portal on line 11 (Set Variable...), FileMaker doesn't know which row on the portal you are referring to, so chooses the first row (hence all your emails refer to the leasing template).

To resolve this set the variables $$TemplateSubject and $$TemplateBody before moving the context over to the other portal, i.e. put them at the beginning.

Comment and bcooney make valid points, in that you may not need a join table. It's hard to second guess how a solution will work, and from what you've said in your follow-up question, you won't need one.

Edited September 21, 2016 by rwoods

[bcooney]

I would suggest that you pass the Subject and Body as script parameters in the button that they click in the templates portal. No need to have $$vars. 

[miltont]

Thanks rwoods,

I placed the set variables at the start of the script (as well as tidying up the script in general) see below.

It is still selecting the data from the first row in the left hand portal?

Thanks bcooney,

I have never set up a script parameter before, how would I structure this in the specify calculation box?

Thanks heaps!!

[rwoods]

Hi Miltont

Having set the variables at the beginning of the script, you don't actually use them!

The line Set Field[OwnerEmails::Description etc etc] needs to use $$TemplateSubject as the second part of the calculated result. You have referenced the OwnerEmailTemplates portal again, and are having the same problem as you had originally i.e. the selected portal row has been lost.

Same for the line after where you set OwnerEmails::Note

You could use the debugger to help you here (if you have FileMaker Pro Advanced). This would allow you to keep an eye on what FileMaker is seeing. You could set a watch on OwnerEmailTemplates::EmailSubject and you would see that FileMaker had lost the correct portal row by the time you are trying to use that field half way down your script.

On the subject of script parameters, bcooney is suggesting that you could call your 'Owner email from portal **' script with a script parameter that passes the Subject and Body. Todd Geist has a good article about passing multiple parameters at https://www.geistinteractive.com/2011/07/18/passing-multiple-parameters/. It may be troublesome though to do that if your email 'body' has carriage returns in it (some of Todd's techniques use the 'List()' function, where carriage returns have a special meaning).

FileMaker has help files about the general topic of sending Script Parameters, maybe http://www.filemaker.com/help/12/fmp/html/create_script.13.35.html would help on that.

What you are doing currently will work with the tweaks I suggest above (hopefully!)

Hope this helps.

[miltont]

Thanks rwoods,

That fixed it, pretty obvious once it is explained....

One last item (hopefully!) on the second last line in the script is: Go to Field (OwnerEmails::Author for Email) which uses a Drop Down List with values from Staff::First Name.

As there are a number of staff, I have added a field called Department, which uses a second DDL with custom values. Accounts, Admin, Property, Sales.

How do I limit the first DDL (for Staff::First Name) to only show staff names that equal Admin? 

[rwoods]

Create a new table in your relationship graph based on the Staff table. Call it something like 'staff_matchingDeptInOwnerEmail'. Then create a relationship from your OwnerEmails table to that new 'staff_matchingDeptInOwnerEmail' table occurrence where the chosen Dept in your second DDL from the OwnerEmail table is related to the Dept in the 'staff_matchingDeptInEmail' table.

Then create a new Value List, choose the 'use Values from Field...' option and in the resulting dialog select the 'staff_matchingDeptInOwnerEmail' table and then the 'Name' field. Further down, next to 'Include only related values starting from...' select your OwnerEmail table.

This should give you a value list that only shows the names of the staff in the relevant department.

Do feel free to 'like' any posts that you found helpful in this topic by the way, from any contributor ;-)

Edited September 23, 2016 by rwoods

[miltont]

Thanks rwoods,

I tried creating a relationship between the OwnerEmails table and the copy of the Staff table named StaffDepartment, but it did not work, it only shows the first staff members name.

I am unsure which fields to use for the join and should the join be based on =

I have attached the join and the value list below.

So recapping:

OwnerEmails::AuthorForEmail is a Drop Down List based on Staff::FirstName

What I require is that the list of staff first names is 'filtered' by Staff::Department, which is also a drop down list which is based on custom values.

(could using a DDL with custom values be the problem)?

 

Thanks again!