| Author |
Message |
psalm19
Groupie
Joined: 19 Feb 2009
Online Status: Offline
Posts: 48
|
   Topic: Formula Help?
Posted: 19 Feb 2009 at 3:08pm |
Running CR X
I need the report to show Totals for RepsToCredit that equal John, Sally, Marky excluding FirstNames which match ABC or Toshiba, 'House' is a running formula I'd like to include in this formula, if possible but the RepToCredit must be null otherwise ignore. I'm thinking what I'm trying to do is possible but am having difficulties with the formula.
That's probably very confusing but maybe this sample data will help.
RepsToCredit FirstName Quote_Amount
John Doe John $200.00
Sally Sue $150.00
Marky Mark Marky $500.00
House $300.00
John Doe John $250.00
Formula:
({tblServiceOrders.RepToCredit} = [3, 280, 282])
and
not ({tblRepsfromAccount.FirstName} = ["ABC", "Toshiba"])
and
[({tblRepsfromAccount.FirstName} = 'House') and isNull ({tblServiceOrders.RepToCredit})]
House Running total formula below:
({tblRepsfromAccount.FirstName} = 'House')
and
isNull ({tblServieOrders.RepToCredit})
|
 IP Logged |
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 20 Feb 2009 at 6:34am |
so far, you formula is just an if statement of who you want, it doesn't do anything, and it will never return anything...you are looking for RepToCredit in the array and being null...contradictory statements
it would seem that you would want something like:
if (({tblServiceOrders.RepToCredit} = [3, 280, 282])
and
not ({tblRepsfromAccount.FirstName} = ["ABC", "Toshiba"]))
OR
[({tblRepsfromAccount.FirstName} = 'House') and isNull ({tblServiceOrders.RepToCredit})] then
someVar := someVar + {tableName.Quote_Amount}
You will need to have a display formula to display the shared variable, and perhaps one to reset it.
Hope this helps.
|
|
IP Logged |
|
psalm19
Groupie
Joined: 19 Feb 2009
Online Status: Offline
Posts: 48
|
Posted: 20 Feb 2009 at 7:54am |
lockwelle,
Thank you for your help! After inserting the correct table for the quote amount I placed the formula in the report and it's complaining at "someVar" is this supposed to be literal or something I'm supposed change it to?
Error: A number, currency amount, boolean...expected here
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 23 Feb 2009 at 6:05am |
I was thinking that you wanted a total of some kind and SomeVar is just a variable.
If you want a total, you will need to declare a shared variable which would look like and this would go at the top of the formula:
shared numbervar SomeVar;
if you just want to see a number then remove the references to SomeVar and the 'then' portion would just be the {table.field} value.
Hope this helps
|
|
IP Logged |
|
psalm19
Groupie
Joined: 19 Feb 2009
Online Status: Offline
Posts: 48
|
Posted: 23 Feb 2009 at 8:25am |
lockwelle,
Your thinking was right, I do want a total. I'm ignorant when it comes to variables. When I moved the variable to the top of the formula I no longer receive the error but when I run the report it doesn't display a total, it is blank. Below is how the syntax looks in my formula.
WhilePrintingRecords;
shared numbervar SomeVar;
if (({tblServiceOrders.RepToCredit} = [3, 280, 282])
and
not ({tblRepsfromAccount.FirstName} = ["ABC", "Toshiba"]))
OR
[({tblRepsfromAccount.FirstName} = 'House') and isNull ({tblServiceOrders.RepToCredit})] then
{tableName.Quote_Amount}
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 24 Feb 2009 at 6:09am |
OK, if you want a total, you need 3 formulas.
1) will be at the beginning of the group, and resets the total to 0
shared numbervar SomeVar := 0;
"" //I put the empty string so that there isn't a zero displaying
2) will be in the group footer (where you want to display the total)
shared numbervar SomeVar
3) will be where you want to gather the vaules (usually the detail band)
WhilePrintingRecords;
shared numbervar SomeVar;
if (({tblServiceOrders.RepToCredit} = [3, 280, 282])
and
not ({tblRepsfromAccount.FirstName} = ["ABC", "Toshiba"]))
OR
[({tblRepsfromAccount.FirstName} = 'House') and isNull ({tblServiceOrders.RepToCredit})] then
SomeVar := SomeVar + {tableName.Quote_Amount} //increments the variable
Hope this helps
|
|
IP Logged |
|
psalm19
Groupie
Joined: 19 Feb 2009
Online Status: Offline
Posts: 48
|
Posted: 24 Feb 2009 at 7:33am |
I appreciate you staying with me on this! I created the first formula and called it SomeVar and created the second and called it NumberVar. Since the total is displaying in Report Footer D I placed SomeVar in Footer A and NumberVar in Footer D. I checked to ensure the 3rd formula looks identical to your example when I check it, it complains (highlights) the line below with the following message, "A number is required here".
SomeVar + {tableName.Quote_Amount}
I'm not sure where I went wrong following your instructions. 
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 24 Feb 2009 at 8:53am |
odd question, is the Quote_Amount a string value or a number...
did you replace the field with the one that has the 'real' table name in it? (I don't think that tableName is going to work)
Hope this helps
|
|
IP Logged |
|
psalm19
Groupie
Joined: 19 Feb 2009
Online Status: Offline
Posts: 48
|
Posted: 24 Feb 2009 at 9:28am |
|
Quote_Amount is a "currency" field and yes, I replaced the field with its proper name (lol) which is tblQuotes.TotalNetSell.
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 25 Feb 2009 at 6:05am |
had to ask ;) only other thing that I can think of is that SomeVar needs to be initialized...or change the names of the formulas...should effect anything, but you never know.
try changing the line to:
shared numbervar SomeVar :=0;
if the error goes away, SomeVar needs to be initialized before use, obviously here is not the place.
Hope this helps
|
|
IP Logged |
|
|
|
