Some people may prefer to receive reports as direct email messages. The key to a successful mailed report is to make sure that a valid e-mail address can be recognised by the system. The only way to do this is to use the derived fields to correctly address the mail message. The mail is addressed using three fields which are set within the Derived Tab.
Note: Mailing reports is only intended for short reports. Reports over 80 lines long are split and mailed in several messages. Long reports should be sent to a main spooler or batched so that the spooler functions can be used to control the output.
See also:
Derived Tab for more information on notifying users via email.
The three derived fields variable names used for addressing the e-mail messages are as follows.
Note: Correct spelling is essential.
This is the ID (the system name for the recipient) you would use if you send them mail, it is normally their login ID. This field must not be blank. Make sure that it is checked and completed by the derived field logic in all cases.
This is the e-mail ID of a person who should be copied with the report mail content. It is not mandatory and can be ignored if it is not required.
This is the title for the e-mail message. It must be less than 50 characters long. Make the message informative and as short as reasonably possible. If this variable is left blank the message will still be sent.
These examples illustrate settings for mailed reports and also how more complex table links can be established using derived fields. You will need to understand how a Sales Ledger account can link to a marketing company or target record. Also note how the Sales Ledger account can name sales exec codes on the Other details tab.
Note: Successful use of the Report Generator often depends a reasonable understanding of how the overall system works in order to apply some investigative skills. This is not to say that all Report Generator users must be experts, only that they should spend some time reviewing the relationships between the database tables you want to use.
Here is a simple report to be sent to one user. A copy of the report goes to a second user and a subject message is added to the mail message.
The content of the report is not important, although the report is kept short with the minimum of extra information on the format. The only part of the report shown is the derived tab section.
The only other part of the report mentioned here is the sequence tab. The mail output type of report looks at the sequence and send individual mail messages to the owner of each section of the report. The owner of each section is defined by the content of the field being used to define page breaks in the report. In this case it is important that the sequence is kept simple, with no breaks.
Notice that derived fields do not have names in this case. Remember that derived field names are optional and only need to be used if the derived field is going to be used in another part of the report, or if it is to be used within another derived field calculation. In this case the variables being set are not going to be used in any other part of the report so names are not required.
| Name | =...... |
| email_to$ = "pjj" | |
| email_cc$ = "cew" | |
| email_subject$ = "Top twenty debtors" |
The first derived field sets a value to the variable email_to$. The value being set is surrounded by quote marks and is case-sensitive. In this case it will nearly always be lower-case.
A copy of the report is being sent to user cew and therefore the variable email_cc$ has been set accordingly. Again, this is case-sensitive and lower-case should be used.
It is important to note that it is necessary to sort mail output reports on a derived field and for this purpose another field
recip$- is created as follows:
| Name | =...... |
| recip$ | email_to$ |
This derived field has been named -recip$ and it is used in the sequence tab as the primary sort column.
The sequence tab will therefore be constructed as follows:
Sequence type: Bespoke Direction Ascending
Table ! id: 0
Column: recip$
Break: set to a tick
Subtotal: set to a tick
It is possible to use additional sort criteria as required. For example, if the report was concerned with parts stock records then it would make sense to make the second element in the sort sequence the part number-essentially sequencing the body of the Email into part number order.
In that case, the sequence tab would be constructed as follows:
Key path: Bespoke Direction Ascending
Table !id: 0
Column: recip$ table ! id 1
Break: set to a tick Column PARTNO
Subtotal: set to a tick Break unset
This example assumes that the SM. Stock table has been used as the prime table.
One characteristic of Email output is that multiple messages may be generated by the system if the report output exceeds the capacity of a single Email message. It is for this reason that we recommend that Email is used for summary type records only.
The second example shown here is where individual users are going to be sent different sections of the report. In this case it is very important to make sure that a derived field is set up to verify the user id to which the mail is being sent actually exists. Agree a default mail ID which will receive items that cannot be correctly set and use a ternary to catch these items as they arise.
An example table list could be as follows.
| SL | ** | trans | SL Transaction | ||||
| SL | ** | accts | SL Accounts | 1 | ACCOUNT | 1 | |
| MK | ** | compy | Company records | 2 | MKMAGIC | 1 | |
| GB | 00 | users | User details | 3 | SALESREP | 3 |
The Sales Ledger transaction table is being searched. This is linked to the Sales Ledger accounts table, which holds a marketing record number. The company record is opened as a third table using this number and then the Sales representative from the company record is used to access your details table.
As the Sales Ledger record holds either a company or target record number, but not both, the example shown will have to be limited to accounts which have the field MKTYPE set to C. If a T is present in this field then a separate report could be set up to pick up these records. The third example shown will show both tables being used.
The derived fields are set as follows.
| Name | =...... |
| email_cc$ = " " | |
| email_subject$ = "Area debtors report" | |
| RECIP$ | !4-USERID$ <> " " ? !4_USERID : "pjj" ) |
| email_to$ = !0_RECIP$ |
Four derived fields are used here, the first two set up the email_cc$ and email_subject$ fields. The third field called RECIP$ is set using a ternary. If your ID from the fourth table, (GB.users), has a value then it is used, otherwise you pjj will receive the item. Finally the variable email_to$ is set to be equal to the derived field RECIP$.
The sequence of this report is primarily set using the derived field RECIP$. The Break and Page check boxes should be selected to cause page breaks, this will cause the system to send each section of the report to the user ID identified in the field RECIP$.
This example is a little more complex. To begin with let us establish what we want to achieve. We are going to identify old or unpaid transactions. We will then send, via e-mail, the resulting report to the Sales Executive responsible for the customer. We must therefore identify the user id of the Sales Executive responsible for that customer to make sure that they action only those items they are responsible for. Establishing the person responsible involves getting the correct user id from the GB.users table. To understand this example it is important to understand the way the Sales Ledger accounts table can be linked to a marketing record.
Each Sales Ledger account record can be associated with a marketing record. Some accounts will link to a target record and some accounts will link to a company record. We need to know which record to look at in order to locate the correct e-mail address. Two columns are set within the Sales Ledger account record, one is MKTYPE which can either be C for a company record or T for a target record. The second field is MKMAGIC which is the magic number for the marketing record.
Target records specify a Sales Executive code for credit chase purposes. If this is the case we will always need to locate the target record in order to identify the correct Sales Executive code.
The problem coped with here is that we will not assume that each e-mail recipient is found via the marketing company table linked to the Sales Ledger account.
In each of the following three cases we need the Sales Executive code so that we can go to the GB.users table to locate the e-mail address for the selected sales executive.
In order to identify this user id we have to make one of three possible choices.
Use the Sales Executive code on the Company record linked to the Sales Ledger Account record (if it has been filled in).
Use the Sales Executive code named within the target record linked to the company record named in the Sales Ledger account record via the column called DBTMAGIC (if it has been filled in).
Use the Sales Executive code named within the target record directly linked to the Sales Ledger account where the marketing type is T.
Consider the following cases:
This assumes the system has been set up in such a way that each Sales Ledger account is linked to a company record. This company record is then used to locate the correct Sales Executive code for e-mail purposes. Either the sales exec column is used, or the company record is used, to locate the correct target record which will lead to the sales exec code required (located via a column called DBTMAGIC).
This assumes the system has been set up in such a way that each Sales Ledger account is linked to a target record. The target record can hold a sales exec code for up to 7 different cost centres, the report will use the sales exec code named against the credit control cost centre.
Where a mixture of company and target records are named a report will need to identify which should be used, this will either be:
The sales exec code from the company record
The sales exec code from the target record linked to that company record which is identified using the company record column DBTMAGIC (this is the target to be used for accounting type contacts)
Or finally the target record named directly within the Sales Ledger account record.
This all looks a little confusing first time around. Remember that reports cannot be written unless you understand the subject matter. Spend some time getting it clear in your own mind before trying to write the report. It often helps to forget the keyboard and use a pen and paper to map out the report first.
Work it backwards if you like, start with the requirement for an e-mail ID. This has to come from the GB.users table. To open this table you need a sales exec code. The sales exec code can be found in the Sales Ledger account table in a column called SALESREP. If the account is linked to a marketing company record you may be able to use the sales exec column in the company record. If the marketing company record links to a credit control target then the target record will hold the sales exec code for credit control purposes. It is doubtful that your system will use all three of these methods so find out which of the methods is used and write the report accordingly.
An example table list could be as follows:
| SL | ** | trans | SL Transaction | ||||
| SL | ** | accts | SL Accounts | 1 | ACCOUNT | 1 | |
| MK | ** | compy | Company records | 2 | MKMAGIC | 1 | |
| MK | ** | targt | Target records | 0 | 1 | ||
| GB | 00 | users | User details | 0 | 3 |
The Sales Ledger transaction table is being searched. This is linked to the Sales Ledger accounts table, which holds a marketing record number. The company record is opened as a third table using this magic number, even though it may not relate to a company record. The target table is opened using a derived link, as is the User details.
As the Sales Ledger record holds either a company or target record number, but not both, the example shown here will need to identify which of the marketing records are used. Once the correct one is identified, the derived link will make use of the sales representative code in the relevant record to fulfill the derived link logic and locate the correct user.
The derived fields could be set up as follows:
| Name | =&ldots;.. |
TARGET USER$
USER$ RECIP$ |
!4_MAGIC=(!2_MKTYPE=T?!2_MAGIC:!3_DBTMAGIC) (!4_COSTCENT(PB)=C?!4_EXEC(PB):!0USER$) !0_USER$= AND !2_MKTYPE=C?!3_EXEC:!0USER$) (!5_USERID<> ?!5_USERID :pjj |
The derived fields can be explained as follows:
The first derived line examines the Marketing record type field in the Sales Ledger accounts table. If it is T the magic number from the Sales Ledger accounts table is used, otherwise the magic number from the column DBTMAGIC in the company record is used. The number selected is assigned to magic number column for table 4, therefore establishing a derived field table link.
The second line sets up a counter which will run up to seven times. This is the number of different cost centres that can be held on each target record. A variable called PB is used as a counter. Users are advised not to use a variable I which is common in these cases.
The third line checks each element of the cost centre column in turn. There are up to seven of these columns and we are looking for the one which has a value of C, which in the case of this example represents the cost centre for credit control. Check this on your own system to make sure the letter C is used for this purpose. If it is not then substitute the correct letter. If the correct cost centre letter is found, the sales exec code that corresponds with this occurrence is used, otherwise the derived field value is left unchanged.
The fourth line increments the counter to make sure that each of the seven cost centre codes are examined.
The fifth line will reset the value of USER$ but only if it is currently blank and the sales ledger field MKTYPE has a value of C. In this case we are checking to see if the preceding checks of the target record were successful. If they were not, we are trying to identify the sales exec code by using the default code on the marketing record.
The sixth line sets the derived table link to open table number 5, the GB.users table.
Derived lines seven and eight set values to the email_to$ and email_subject$ fields.
The ninth derived line checks to see if a user id has successfully been loaded in table number five and assigns it to the derived field called RECIP$. This field is used in the Sequence tab. If the fifth table was not loaded correctly then a default user ID is used so that the report will not fail.
Finally in the tenth line the variable email_to$ is set to be equal to the derived field RECIP$.