Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

This topic is 6285 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted (edited)

I have a table - e_mail_addresses which includes a field:

data_04_opt_in - the point of this field is to store a "1" if they opt in and a "0" if they opt out and has an an auto entry calculation as follows:

If(isempty(unsubscribed::__id;0;1)

Data_04 is "stored" and is used in another relationship to "get a list of opted in e-mails for mailing"

The other table - "unsubscribed" has a field mail_id which links to __id in e_mail_addresses

Unsubscribed is populated by an import of email ids

But data_04 doesn't update.

I may be wrong but I was assuming that this could be done by relationship rather than a script setting the value of data_04 as part of the import processs.

Incidentally if i run replace field with calc and use that exact calc it updates - so is there a way of forcing the relationship to trigger that change?

Once again thanks in advance for any pointers or advice

Simon

PS apologies this was in the relationships forum just now - which I assume is wrong

Edited by Guest
Posted

Auto-enter calculations do not update when a related field is modified (and for a good reason, too). You'll have to figure out some other way to accomplish whatever this elaborate setup was meant to do.

Posted

I am sorry but when I read all the other posts on here that get very helpful responses I do wonder if there is something in the way I write my queries that puts people's backs up...

Is there something in the question that is so very stupid that it warrants an opaque response?

All I am trying to do is import some unsubscribed e-mail addresses and update my e-mail list with a simple value so that I don't break any spamming laws.

If it should be done in another way I really all ears...

If you could direct me in more positive direction that would be fantastic.

Posted

The truth is I hesitated before answering at all, because the most I could do is tell you it won't work your way - while having no idea what "it" is. I am still in the dark about your real purpose. The description "import some unsubscribed e-mail addresses and update my e-mail list with a simple value" is rather vague.

I realize it's difficult to find the right balance between what's important and what's not, when one doesn't know the answer. I'd suggest it's better to err on the side of more details. But the most important thing is the purpose - and that rarely requires any knowledge of Filemaker.

Posted

Well I have used some script steps in the import script to update said value - this will probably work because its such low volumes that I am dealing with.

Nevertheless I am sure that there is a more elegant FM way to achieve this update - and from a learning point of view I would love to know how this should have been done.

IF anybody can point me in the direction of some material that would help on this whole relationship vs calculation thing that would be very much appreciated

John, thanks for your help in the past - I hope I can count on your sage comments in the future too.

I would hate to give up on filemaker

thanks

Simon

Posted (edited)

Hi

My last post crossed with yours.

My apologies - I always worry that if I write too much then nobody would want to get involved.

I do feel very stuck with this application - I am not a full time FM developer (as you can tell, I know) but, I am being asked to make it do some clever things and the truth is I really do enjoy it - I just don't enjoy the lack of knowledge.

It does seem that to make FM work is really about the unpublished subtleties and that, of course, is frustrating from an novice point of view.

My previous approach was too clever for its own good - perhaps the right way was use the script approach - what I have ended up with is broadly as follows:

Loop

Import (from outlook) unsubscribed::e-mail_add_id

Commit record

gtrr - (from) main e-mail table

set field (opt in/out) to 0

go to layout (original layout)

get next record

exit when no more

End loop

It works - but is it any good? what are the obvious problems?

Cheers

Simon

Edited by Guest
Posted

Here is something I wrote a while back regarding stored values and relationships:

http://www.fmforums.com/forum/showtopic.php?tid/172085/post/187127/2/#187127

The post is a sort of a summary, but you could probably benefit from reading the entire thread too.

Posted

Re your script: I don't think anyone can say anything intelligent about it, because once again, the two main things - namely the context and the purpose - are missing.

Posted

Is there something in the question that is so very stupid that it warrants an opaque response?

You need to remember that even the most talented developer falls under this:

http://www.sciam.com/print_version.cfm?articleID=00010347-101C-14C1-8F9E83414B7F4945

...where it can't be stressed too much, that if something doesn't leave a trace of similarity to these mental structures the article talks of, if say some too arbitrarily chosen incidents far from each other won't interpolate properly, will the answer be absent. Be honest to yourself if your reasoning lacks method as such, don't expect it to be mirrored in the potential repliers mind.

Being good at something requires method and structure in the knowledge, not just a good memory ... think of people with Downs syndrome's abilities to remember the most fantastic details, but when it comes to "...read the game" are they falling behind!

What can you do about it? Give the forum a sense of a welldefined MISSIONSTATEMENT and not just some atomicly felt flaws, you wish to brush off your shoulders! ...Or as Michael just said context and purpose!!

--sd

Posted (edited)

Michael, Soren

I hope the following is a better explanation.

I have two tables: the first called e-mail-adds which is used to store a master directory of e-mail addresses.

It has (amongst others) the following fields:

__id

Data_01_email_address

Data_04_opt_in_flag

and another called unsubscribed-email-adds which is used to store the id's of the e-mail addresses where people wish to unsubscribe from the mailings.

which has the following fields

__id

e_mail_add_id

These two tables are linked as follows:

e-mail-adds::id = unsubscribed-email-adds::e_mail_add_id

I send out e-mailings (based on a found set from a TO of e-mail-adds where the relationship also includes Data_04_opt_in_flag = 1). The 1 being stored in a global field in a related table of "companies").

Ie what i actually do is find the companies I want (geographically, sector whatever) and then gtrr using e-mail-adds which gives me a list of all "opted in" e-mail-addresses for those companies.

Each e-mail contains an "unsubscribe link". The link includes a unique query string parameter which is the e-mail-adds::__id for that e-mail.

If somebody clicks on the unsubscribe link an e-mail is sent to me which includes the e-mail-adds::__id in the "body" of the "unsubscribe" e-mail.

I get the unsubscribe e-mails and import the unsubscibed e-mail-add-ids from the body of each e-mail into the unsubscribed-email-adds table.

So far so good

The last part of the picture is to update the e-mail-adds::Data_04_opt_in_flag field to a "0" if the e-mail-add-id now appears in my unsubscribed-email-adds table. Ie so that future found sets of e-mail adds won't include the opted out e-mail addresses.

At the moment the script does the following:

Go to layout "unsubscribed-email-adds"

Loop

New record request

Get next e-mail details from outlook

Set field unsubscribed-email-adds::e_mail_add_id=body of email (ie the e-mail id)

Commit records

GTRR

Set field e_mail_adds::Data_04_opt_in_flag=0

Go to layout"unsubscribed-email-adds"

(exit when found count = count of unsubscribes e-mails)

End loop

I started out trying to update the field based on the relationship - but if it has to be done via a script is this a good way to do it?

Many thanks

Simon

PS I should explain that the e-mail integration with outlook is handled by Outlook Manipulator a FM add-on from Productive Computing but my question does not relate to that add-on.

Edited by Guest
Posted

Let me see if I understand this correctly (my brain refuses to work with your table/field names):

Three tables: Companies, Subscriptions and Cancellations.

A company has many subscriptions (one to many, linked by CompanyID).

A subscription can have a cancellation (one to one, linked by SubscriptionID).

You want to start with a found set of companies, then find the related subscriptions that do NOT have a cancellation.

If so, there are a number of ways to do this, I'll name a few. Which one is best for you depends on how many records are there in each table (and in each new batch of cancellations), how often you do this, how many users are using the solution, and your personal preference.

[color:orange]1. GTTR & FIND

This starts by getting all subscriptions of the found companies, then removes the cancelled ones:


Go to Related Record [ from Subscriptions; Match found set ]

Constrain Found Set [ Omit Records; Criteria: Cancellations::SubscriptionID: “*” ]
 

This is probably the simplest and the most straightforward method.



[color:orange]

2. FILTERED RELATIONSHIP

Here we define a new relationship from Companies to a new occurrence of the Subscriptions table, making only uncancelled subscription related. For this we need a calculation field cCancelledIDs in Companies defined as List ( Cancellations::SubscriptionID ). The relationship is then defined as: 





Companies::CompanyID = Subscriptions 2::CompanyID

AND

Companies::cCancelledIDs ≠ Subscriptions 2::SubscriptionID



This is a rather elaborate method. The advantage of this is you can have a portal in Companies showing only uncancelled subscriptions, and a simple GTRR creates a found set.





[color:orange]3. STORED STATUS

This is closest to what you have now. Here we have a stored Status field (Number) in the Subscriptions table, initially set to 1. We want to change it to 0 if a cancellation has been received. The relationship from Companies to Subscriptions (or to another TO of Subscriptions) is filtered by the Status field.



Immediately after importing a new batch of cancellations (while the found set in Cancellations is still the newly imported batch):





Go to Related Record [ from Subscriptions; Match found set ]

Replace Field Contents [ Subscriptions::Status; 0 ]

Note that this can fail if another user is editing a subscription record at the same time. You could use a looped Set Field[] instead of Replace Field Contents[] to catch any errors and return to them later.

This method will provide the fastest find (just GTRR, using only stored fields), but it also violates normalization by duplicating data.

---

Note that none of these methods takes into account the possibility of renewing a subscription.

Posted

__id

Data_01_email_address

Data_04_opt_in_flag

I wondered what else is stored in your fields?

Data. In computational systems data are the coded invariances. In human discourse data are that which is stated, for instance, by informants in an empirical study. Information is related to meaning or human intention. In computational systems information is the contents of databases, the web etc. In human discourse systems information is the meaning of statements as they are intended by the speaker/writer and understood/misunderstood by the listener/reader. Knowledge is embodied in humans as the capacity to understand, explain and negotiate concepts, actions and intentions.

From - http://www.success.co.il/is/dik.html

Are you trying to tell us that you prefix calculation fields with calc_ ...or??

--sd

Posted

Michael,

Thank you so much - this was really food for thought. You have absolutely got the requirement right.

In answer to your points:

There are about 27,000 companies in the company table and at the moment about 16,000 e-mail addresses so its not huge. There are only 5 users on the system. Nobody else would manually handle cancellations - it should be automatic. There are no cancellations (yet!)

The "cancellations" are collected naturally. My intention was to run the script that handled the cancellations as part of the emailing script - ie all the cancellations since the previous e-mailing would be processed just before the next e-mailing.

I hadn't planned on keeping the file of cancellations - hence the status field but I do like the idea of not having to maintain the status field

So your method 2 is very tempting. Would cCancelledIds be a global field - how would it update? Is this a different question to the one I came to the forum with? ie does it need to be set by a script? (I really don't know the answer to these questions and I do like the simplicity of the solution)

But with the status field as in (3) it is very easy to adjust a subscription record though I don't think the need to reinstate will occur very often.

I will expriement with the replace field contents - just a bit worried that I won't have a found set at the end of the import but that will be self evident!

Michael, thank you for putting so much time into this topic.

Cheers

Simon

Posted

Those are not huge numbers, but large enough to warrant testing for speed. Personally, I would tend towards the first method, because it's practically zero setup and maintenance. Even it does get a bit slow as the number of records grows, it will be only noticed when it's time to send out a new e-mail (and I imagine that by itself would take some time, so the added delay would be virtually unnoticeable).

Not keeping the received cancellations is IMHO not a good practice. Even with a stored status field, you should keep a trail.

Re method 2: cCancelledIDs should be an unstored calculation field, with the result set to Text. Note that this uses a relationship that depends on the results of another relationship. Therefore a window refresh (+flush cache) may be required after modifying records in the cancellations table.

Posted

I have just been through all 3 methods and I suspect that the speed difference would indeed be negligible - especially given that we would only be sending out 500-600 mails at once.

I have just finished re-working the solution to go with your suggestion number 1 - it really is simple and I learned a lot from going through your notes and understanding how it worked.

There was one other massive advantage to number 1 too - it will be relatively easy to add in multiple cancellations (ie if we had multiple subscriptions avaiable) which it would not have been with the "status field join" approach.

BTW - I agree with you and will be keeping the "cancellations" file in tact.

Thanks

Posted

There was one other massive advantage to number 1 too - it will be relatively easy to add in multiple cancellations (ie if we had multiple subscriptions avaiable) which it would not have been with the "status field join" approach.

As long as each cancellation references a specific subscription by its unique SubscriptionID, I don't see the difference.

This topic is 6285 days old. Please don't post here. Open a new topic instead.

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
×
×
  • Create New...

Important Information

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