Results 1 to 8 of 8

Thread: Help with a calculated field

  1. #1
    Member
    Real Name
    Judith Donoghue
    Join Date
    Jun 2006
    Location
    Formby, UK
    Posts
    196

    Default Help with a calculated field

    Hi

    I have been working on a report design for far too long and hope someone can help. The report has rows totaling the number of people booking a course and goes on to display how many people were mailed and calculates the response rate, but if no one booked the number mailed doesn't show, is there an expression that will display the amount mailed even if no one has booked.

    I have attached a zip file - the report is 'test_response_report2 and to run the report there is a saved query called 'mail_test'.

    Any help greatly appreciated.

    Judith
    Attached Files Attached Files

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Help with a calculated field

    Try the volume mailed expression as

    vol_mailed = case(calc->source="Customer",Mailshots->Cust_Vol,calc->source="Mint",Mailshots->Mint_Vol,.T.,0)
    There can be only one.

  3. #3
    Member
    Real Name
    Judith Donoghue
    Join Date
    Jun 2006
    Location
    Formby, UK
    Posts
    196

    Default Re: Help with a calculated field

    Thanks Stan, I have just tried that but it still doesn't show the volume mailed where there hasn't been any bookings (dels_booked field). The figure for the bookings comes from the parent table and the volume mailed comes from the child table - does that cause complications?

  4. #4
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Help with a calculated field

    I've gotten confused.

    The report doesn't show the volume mailed if calc->dels_booked is zero? Or the report doesn't show the whole line for the data source if calc->dels_booked is zero.

    Can you provide a sample mailshots_ref where this is happening?
    There can be only one.

  5. #5
    Member
    Real Name
    Judith Donoghue
    Join Date
    Jun 2006
    Location
    Formby, UK
    Posts
    196

    Default Re: Help with a calculated field

    Hi Stan,

    It doesn't show the whole line, I think I have caused the confusion by starting off the thread saying that the volume mailed doesn't show. There are 2 elements that would show and that is the Amount Mailed and the Cost of Mail.
    On the first page of the report if you look at reference ECT-18-231 you can see in the header row that 1832 is the volume mailed then below there is a row for CUSTOMER showing the amount mailed as 1051 - the difference is the amount mailed were we haven't had any dels_booked - that is what I need to show in the report so the admin people can see which marketing strategies aren't getting a response.

  6. #6
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Help with a calculated field

    Ok, clearer.

    Open the default browse for the set. Sort ascending on mailshot_ref2. Find the ECT-18-231.

    There is only one record in the set that matches. That means there will only be one line on the report for that ref.

    It has a data source of customer but has values in both mint_vol and cust_vol. I'm unclear how there would be a mint_vol quantity without a Mint data source record.

    Short version, you can't have a line on a report for a record that doesn't exist.
    There can be only one.

  7. #7
    Member
    Real Name
    Judith Donoghue
    Join Date
    Jun 2006
    Location
    Formby, UK
    Posts
    196

    Default Re: Help with a calculated field

    Hi Stan,

    You have helped me I think, so what I need to do is swap the parent and child - have the Mailshot table as the parent and the invoice table as the child, that way I will get the mint_vol and cust_col even it there isn't a corresponding record in the child table. Oh well back to the drawing board.

    Thank you Stan

  8. #8
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Help with a calculated field

    You can have both sets. Miniscule overhead and no data duplication. A set is only a definition of the linkage and layouts based on the definition.

    Once you have the new set saved you should be able to copy the report definition to it.

    have the Mailshot table as the parent and the invoice table as the child, that way I will get the mint_vol and cust_col even it there isn't a corresponding record in the child table
    But it will still only be one line on the report.
    There can be only one.

Similar Threads

  1. Calculated field derived from another calculated field
    By trackbed in forum Alpha Five Version 11 - Desktop Applications
    Replies: 19
    Last Post: 06-26-2013, 12:07 PM
  2. Calculated field inside another calculated field
    By Sandy Nichols in forum Alpha Five Version 5
    Replies: 7
    Last Post: 11-14-2004, 01:24 PM
  3. Use of calculated field in calculated fiel
    By pinzgauer in forum Alpha Five Version 5
    Replies: 6
    Last Post: 07-08-2004, 12:06 AM
  4. Calculated Fields vs. Calculated Field Rule
    By Rhett Scott in forum Alpha Five Version 4
    Replies: 12
    Last Post: 07-07-2002, 05:58 AM
  5. How do I force a calculated field to be calculated
    By Susan Avery in forum Alpha Five Version 4
    Replies: 5
    Last Post: 11-18-2001, 03:22 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •