auto-creation of related records (SOLVED)

[rlbryant]

To anyone who is interested, I finally figured out how to do this. Here was my original problem:

<snip>I have written a script that loops through a selection of records and sends out an email (using value of global fields as subject and body). I would like to keep a record of what email was sent to whom so I created a related table (related via an auto-generated, unmodifiable number field) to hold this information. Each time an email is sent I would like to automatically create a new record in the related table and fill it with the sequence number of the current record in the loop, the current date, and the subject of the email. I would then display these records via a portal. <snip>

And here is the script I created:

If [Get(FoundCount)>0]

Go to Record/Request/Page [First]

Set Field [People_Contacts::zGlobal_PrevSequenceNum; 0]

Set Field [People_Contacts::zGlobal_CurrSequenceNum; People_Contacts::Sequence_Number]

Loop

Send Mail [No dialog; To: People_Contacts::Email_Address1; Subject: People_Contacts::Global_Email_Subject; Message: People_Contacts::Global_Email_Body]

Go to Layout ["Communications1" (Communications)]

If [People_Contacts::zGlobal_PrevSequenceNum <> People_Contacts::zGlobal_CurrSequenceNum]

New Record/Request

Set Field [Communications::PC_Sequence_Number; People_Contacts::zGlobal_CurrSequenceNum]

Set Next Serial Value [Communications::Sequence_Number]

Set Field [Communications::Communication Type; "Email"]

Set Field [Communications::Subject; People_Contacts::Global_Email_Subject]

Insert Current Date [select; Communications::Sent]

End If

Go to Layout ["Contacts (Communications)" (People_Contacts)]

Go to Record/Request/Page [Next; Exit after last]

Set Field [People_Contacts::zGlobal_PrevSequenceNum; People_Contacts::zGlobal_CurrSequenceNum]

Set Field [People_Contacts::zGlobal_CurrSequenceNum; People_Contacts::Sequence_Number]

End Loop

Else

Show Custom Dialog ["ALERT"; "There are no target records currently selected."]

End If

Maybe this will be useful to someone else...

rlbryant

[Søren Dyhr]

Initially let it be said ...solutions that works is what we all aim for, and the process - I think it's a Buddist saying that emphases that the journey towards a goal is just as rewarding as getting it.

However should it also be of some value to be show the weak spots in a solution, which doesn't show up at first. One of these is the changed behaviour of networked solutions, where the calculations including globals is clientside heavy opposed to calc's sans globals. Further more to whom are the storage of globals outside the scripts of any use??

I'm considering this by adding this snippet to a lot of tasks where I used to use globals...

If [ IsEmpty ( Get ( ScriptParameter ) ) ] 

Perform Script [ "New Script"; Parameter: Untitled::aField ] 

Else 

...... 

...... 

End If 

Which is a nauthy script recursion which have been banned ever since "Loop" was introduced with ver. 2.1 or was it 3.0. - but since I only loop once - will the damage caused to the stack be insignificant

--sd

[rlbryant]

Hi Soren,

Thank you for responding. I posted a request for help on this several days ago and didn't get any response so I figured it out myself the best I could. I tried using ScriptParameter and a separate script for creating and populating the related records but it didn't work. What was happening is that the related records were getting created but they were all getting the sequence number value of the first parent record. In other words, it seemed as though all the related records got created on the first iteration of the loop. I can't verify that this is exactly what was happening as I don't have Filemaker Developer (with the debugger). Did you actually try creating related records while looping through the parent records -- or have you done this in your own application(s)-- with the code you suggested? I'd certainly like to improve on this script if possible as, if this application should ever be moved to client/server the globals would present a problem. Thanks for pointing this out.

rlbryant

[rlbryant]

Just wanted to report that a developer on another forum showed me a much easier way of doing this without using my hack of checking prev vs curr. If anyone is interested I will post it. Just let me know.

rlbryant

[Søren Dyhr]

If anyone is interested I will post it. Just let me know.

Please do, while I tries to wrap my head around you previous response (I've pulled the url to my desktop and intend to reread when time is availiable)

Meanwhile could you study these 3 techniques:

http://www.filemakerpros.com/NEWROW.zip

--sd

[rlbryant]

Hello Soren,

Yes, the solution I referred to that was provided by another developer uses the third technique outlined in the sample you provided (thank you very much by the way). But it only works the first time you loop through a given set of parent records. After that, any time an email is sent for a parent record that already has a related record, the existing related record is overwritten. I've attached a document with the script and a screen shot of the relationships. If you can tell me where I'm going wrong I would really appreciate it.

My script is a kludge but it works. It solves the problem I was originally having where all the related records got the same parent ID as if they were all getting created on the first iteration of the loop instead of one per iteration.

Thanks again for your post,

rlbryant

Please do, while I tries to wrap my head around you previous response (I've pulled the url to my desktop and intend to reread when time is availiable)

Meanwhile could you study these 3 techniques:

http://www.filemakerpros.com/NEWROW.zip

--sd

Auto_Create_Related_Records.zip

[Ugo DI LUCA]

Without a view of the graph, it may be difficult to exactly understand what is going wrong.

As a rule, you need to make sure there's no valid relationship in order to create this new related record. I would think that an additional global key with a date and Hour field would make sure that the combo Person-Mail-Date-Hour will result unique.

Otherwise, the match will happen to the first related record.

Another technique would be to populate a random key in a global and add this key to the relationship with its twin key being random_k.

Then you'd absolutely make sure each combination is unique.

As a matter of fact, I came up with this construction some days ago for a fellow on a french list, that had some specific mailing needs.

The namings are french but it is a Mailing Generation Utility Module where the user, when in the current Company record (Structure), dynamically pick related persons from a Selection Portal on left (Personnes_choix), and then a Mailing Type in a portal on the right (Mailing_Choix).

Setting the aleak in Envois_Creation (translate Sendings) then creates the new record ready for the mail to be sent.

The g_alea, which is a random scripted key was necessary, as a person could receive the same mailing "type" twice, but as I explained it could have been easier with a gdate and ghour, but unfortunately, this wasn't possible there.

Obviously, this same construction in a loop would be possible.

HTH

[rlbryant]

Hi Ugo,

The attachment I included in my previous post has a screen shot of the relationships. Is that what you mean by the graph?

Thank you very much for the attachments and for your suggestions. I'm working with a non-profit for whom Filemaker was the best solution for a number of reasons. It's great in that with a combination of a template and scripting I've been able to get them a working application that they could pretty easily take over on their own (if I were to be hit by a bus or something :-) ). The way relations are handled though seems very quirky. I'm used to database building applications (such as Access and 4D) where relationships and the use of parent and foreign keys are much more straightforward. In a Many to One relationship, when a NEW Many record is created why does Filemaker even attempt to "match" it with an existing Many record?? I don't understand this logic at all. The only thing it should be attempting to match a new Many record with is its One record and that is very straightforward; you just grab the key of the One record. I don't understand why Filemaker makes this so complicated. But thank you very much for your help.

rlbryant

Without a view of the graph, it may be difficult to exactly understand what is going wrong.

As a rule, you need to make sure there's no valid relationship in order to create this new related record. I would think that an additional global key with a date and Hour field would make sure that the combo Person-Mail-Date-Hour will result unique.

Otherwise, the match will happen to the first related record.

Another technique would be to populate a random key in a global and add this key to the relationship with its twin key being random_k.

Then you'd absolutely make sure each combination is unique.

As a matter of fact, I came up with this construction some days ago for a fellow on a french list, that had some specific mailing needs.

The namings are french but it is a Mailing Generation Utility Module where the user, when in the current Company record (Structure), dynamically pick related persons from a Selection Portal on left (Personnes_choix), and then a Mailing Type in a portal on the right (Mailing_Choix).

Setting the aleak in Envois_Creation (translate Sendings) then creates the new record ready for the mail to be sent.

The g_alea, which is a random scripted key was necessary, as a person could receive the same mailing "type" twice, but as I explained it could have been easier with a gdate and ghour, but unfortunately, this wasn't possible there.

Obviously, this same construction in a loop would be possible.

HTH

[Ugo DI LUCA]

Your attachment only has the script I'm afraid.

[rlbryant]

Hi Ugo,

I'm sorry. I took a screen shot of the relationship graph; I'm not sure why it doesn't appear in the Word document. At any rate, I've been informed that the script is not working because I am not properly using the "dual" (I'm not sure of the proper term) relationship that I've set up using the global ID field in my Contacts table so I will redo the script with my new understanding of the instructions. (I have the relations set up correctly but am not always referencing the right tables in the script.)

Thanks again for your help and input!

rlbryant