AnsweredAssumed Answered

SQL with if-function instead of FM tools?

Question asked by kip on Feb 18, 2018
Latest reply on Mar 30, 2018 by kip

Hi there,

 

I am looking for some SQL-help to substitute a FM only script.

What I am trying to to is to get a simple mailing list like function into my database.

 

Table A contains personal information (id, name, last name, mail office, mail private, etc.)

Table B contains projects (id, name, N:M-relationship to table with personal information)

Table C contains the names of the mailing lists  (id, name, project_id)

Table D contains people in mailing list (id, mailinglist_id, person_id, code for mailing address)

 

I use "code for mailing address" like this:

1 for "to / office mail address"

2 for "to / private mail address"

3 for "cc / office mail address"

4 for "cc / private mail address"

 

My question is: how can I use SQL to

- gather all people who are in a chosen mailing list

- check which mail address to use (i.e. "code for mailing address")

- generate two lists for "to" and "cc" recipients

 

May be my approach is totally wrong? Then I would appreciate a hint who I could improve the database.

Otherwise, I would highly appreciate some help with the SQLing of the script.

 

kip

 

 

PS. so far I use FM only tools

works fine and gets the result wanted,

but due to the searches, windows and loops it's quite slow:

 

- geht mailing List ID

- fix window

- go to layout based on table D (people in mailing list)

- search for entries with the mailing list ID (all people who should receive a mail)

- go to first entry

- loop

- - set variable (person ID)

- - set variable (c)

- - open new window

- - go to layout "person"

- - search for person ID (using the variable above)

- - when "code for mailing address" = 1

- - set variable ($To_list ; Value: List ($To_list ; PER::eMail_office))

- - end when

- - when "code for mailing address" = 2

- - set variable ($To_list ; Value: List ($To_list ; PER::eMail_private))

- - end when

- - when "code for mailing address" = 3

- - set variable ($CC_list ; Value: List ($CC_list ; PER::eMail_office))

- - end when

- - when "code for mailing address" = 4

- - set variable ($CC_list ; Value: List ($CC_list ; PER::eMail_private))

- - end when

- end loop

- close window

- display dialog ("$To-list & " " & $CC_list")

Outcomes