Compiling related records 
Author Message
 Compiling related records

I do some volunteer work for a foundation, including database design. I have
a series of related databases, 2 of which are involved in this question. I
am running Filemaker 4.0v2 and the databases are set up as follows

Main database
    contains general information about people and serves as a viewer to
other related databases

Email database
    maintains our records of people's email addresses with most people
having only one but some people having as many as 3 or 4

Requirement:
    The person responsible for collecting email addresses needs to be able
to maintain multiple email addresses but we also generate reports through
the main database that combine all sorts of different fields through a
calculation field, the definition of which is attached below, and send that
out through the Filemaker email command. This allows the foundation to
quickly send out requests for corrections of personal info every so often.
As set up, the calculation only pulls the FIRST email address from the
related database. We would like to pull all of the email addresses into the
calculation and separate each with a comma so the person receiving the
report could see all current email addresses maintained in the email
database.

How might I do this?

Damian Brennan

text of calculation definition
"Name: " & First name & " " & Last name & "?" & "Address used for snail
mail: " & If( Active Address="" or Active Address="Permanent", "Permanent",
If(Active Address="Business", "Business","Temporary")) & "?Perm address:?"
&"   "& perm address1 & If(perm address2="","","?   " & perm address2) & "?"
& "   " & perm city & " " & perm st & " " & perm zip & "?" & "   " & perm
country & "?" & "?Phone Numbers, etc?"&"   Home: "&homeph1 & "?"&"   Other
number 1: " & tmpph1 & "?   Other number 2: "&tmpph2 & "?   Fax Number: " &
faxph1 & "?   Email: " & crs email list::email address & If(temp address
1="" , "??No temp information provided - please submit if applicable?" ,
"??Temp Address: Valid until "& If(temp until="" and temp address 1<>"","
*** UNKNOWN *** ?IN ORDER TO GUARANTEE PROPER MAILING OF MATERIALS TO YOU,
PLEASE VERIFY THE TEMP INFORMATION BELOW AND SUPPLY A DATE WHEN WE SHOULD
STOP USING THIS ADDRESS?",DateToText(temp until)) & "?" &"   " & temp
address 1&If(temp address 2="","?","?" & "   " & temp address 2 &"?") & "
" & temp city&" " &temp st&" "  & temp zip&"?" & "   " & If(temp country=""
or temp country="USA","", temp country & "?")) &  If(bus address 1<>"" or
bus name<>"","??Business Info:?" & "   " & bus name & "?" & "   " & bus
address 1& If(bus address 2="" , "" ,"   " & bus address 2) & "?"  & "   " &
bus city &" " & bus st & " " & bus zip & "?" & "   " &  bus country &"?
Business phone: " & bus ph & "?   Business fax: " & bus fax & "?   Business
email: " & bus email & "?   Telex:   " & bus telex,"?No business info
given - please submit if applicable")



Sat, 26 May 2001 03:00:00 GMT
 Compiling related records

Quote:

> I do some volunteer work for a foundation, including database design. I have
> a series of related databases, 2 of which are involved in this question. I
> am running Filemaker 4.0v2 and the databases are set up as follows

> Main database
>     contains general information about people and serves as a viewer to
> other related databases

> Email database
>     maintains our records of people's email addresses with most people
> having only one but some people having as many as 3 or 4

> Requirement:
>     The person responsible for collecting email addresses needs to be able
> to maintain multiple email addresses but we also generate reports through
> the main database that combine all sorts of different fields through a
> calculation field, the definition of which is attached below, and send that
> out through the Filemaker email command. This allows the foundation to
> quickly send out requests for corrections of personal info every so often.
> As set up, the calculation only pulls the FIRST email address from the
> related database. We would like to pull all of the email addresses into the
> calculation and separate each with a comma so the person receiving the
> report could see all current email addresses maintained in the email
> database.

> How might I do this?

Depends on how your multiple emails are set up. If they are in separate
records, you might consider a self-relating calc field that will pull
them into ONE field (hidden but there) in each record for that person.
If they are in repeating fields, you can use the GetRepetition (field)
in your humungous calculation (I snipped that because it made my teeth
hurt just to look at it, not that it isn't a thing of beauty ;).

If they are in separate Email1, Email2 fields...You can use the
If(IsEmpty....) functions to only put commas if the fields exist and not
otherwise clutter up your messages with unneeded commas.

Hopefully one of these scenarios will help.

--

Allen & Allen Semiotics                          Web & Graphic Design
Long Beach CA USA               Filemaker Pro Consulting - Member CSA
562.938.7890  fax 562.938.7891            Custom Solutions & Training



Sun, 27 May 2001 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Appending related records to two related tables

2. How can I get records WITHOUT relating records in another table

3. Problems with records and related records

4. Problems with records and related records

5. How to copy a field from one record to a related record in the same database

6. Using one record to access two (or more) records in another related database

7. NEWBIE: Deletinge from related record on DELETE

8. NEWBIE: Deletinge from related record on DELETE

9. newbie: DM getting related records

10. Inserting records into related Tables using a SPROC

11. Deleting record from related table

12. mv related records into another table


 
Powered by phpBB® Forum Software