Conditional calculation in Smartlist Builder

In 9.0 Smartlist Builder calculated fields, I am trying to evaluate the State 
field in the SOP 30300 to return a value. Ex.

CASE
When [sales transaction amounts history.state] = 'OH', 'KY', 'IN' then 
'Midwest'
Else 'Other'
END

It only works if one state is listed as the condition. What is the proper 
syntax for the above if I want to list multiple states. Thanks


0
MES (112)
12/21/2008 4:12:05 PM
greatplains 29623 articles. 3 followers. Follow

2 Replies
638 Views

Similar Articles

[PageSpeed] 9

MES,

I believe the syntax is:

case
when {Sales Transaction Amounts History:State} in ('OH', 'KY', 'IN')
then 'Midwest'
else 'Other'
end

-- 
Victoria Yudin
Dynamics GP MVP
Flexible Solutions - home of GP Reports
http://www.flex-solutions.com/gpreports.html
blog: www.victoriayudin.com


"MES" <MES@discussions.microsoft.com> wrote in message 
news:2A0FA349-B2F9-465E-912D-BC8A50788EE9@microsoft.com...
> In 9.0 Smartlist Builder calculated fields, I am trying to evaluate the 
> State
> field in the SOP 30300 to return a value. Ex.
>
> CASE
> When [sales transaction amounts history.state] = 'OH', 'KY', 'IN' then
> 'Midwest'
> Else 'Other'
> END
>
> It only works if one state is listed as the condition. What is the proper
> syntax for the above if I want to list multiple states. Thanks
>
> 

0
victoria (3340)
12/21/2008 4:41:58 PM
That worked perfectly...thank you for your help!



"Victoria [MVP]" wrote:

> MES,
> 
> I believe the syntax is:
> 
> case
> when {Sales Transaction Amounts History:State} in ('OH', 'KY', 'IN')
> then 'Midwest'
> else 'Other'
> end
> 
> -- 
> Victoria Yudin
> Dynamics GP MVP
> Flexible Solutions - home of GP Reports
> http://www.flex-solutions.com/gpreports.html
> blog: www.victoriayudin.com
> 
> 
> "MES" <MES@discussions.microsoft.com> wrote in message 
> news:2A0FA349-B2F9-465E-912D-BC8A50788EE9@microsoft.com...
> > In 9.0 Smartlist Builder calculated fields, I am trying to evaluate the 
> > State
> > field in the SOP 30300 to return a value. Ex.
> >
> > CASE
> > When [sales transaction amounts history.state] = 'OH', 'KY', 'IN' then
> > 'Midwest'
> > Else 'Other'
> > END
> >
> > It only works if one state is listed as the condition. What is the proper
> > syntax for the above if I want to list multiple states. Thanks
> >
> > 
> 
0
MES (112)
12/22/2008 2:14:05 PM
Reply:

Similar Artilces:

Today Conditional Format
I have a column which calculated a date based on 3 working days from a date input into a diff column. What I want to do I have the calculated date appear bold if that date has passed based on the current date. For example if I input 01/08/11 the next column calculates 04/08/11 (3 working days). I want the caluclated date to appear bold when I open the spreadsheet on the 05/08/11 ro show the calculated date has passed. I can use conditional formatting but can't get the syntax right Regards Andy Win XP Pro Office 2010 Andy Roberts presented the following explanation : > I ha...

calculated column in pivot table
Can I create a calculated column in a pivot table from two other columns. I have tried creating formulas but that does not seem to do the trick. The answer is yes. For anything less general, we'll need specifics on what you tried, and what happened. "does not seem to do the trick" does not give us much to go on. Regards, Fred "freeriderxlt" <st.jdaich@gmail.com> wrote in message news:97a60a38-262d-4a91-9474-7c26d8c44be3@a16g2000pre.googlegroups.com... > Can I create a calculated column in a pivot table from two other > columns. I have trie...

Isinteg
I ran the following on a private store that had the following size (.edb - 39,816,264 & .stm 35,145,736). On the first pass it took approx. 18 hours to complete, now on the second pass I am well over 24 hours and it is just a little over half a way done. This is exchange 2003 running on windows 2003. Is this normal? I ran the isinteg with -fix -test alltests. -- Eric Sabo NT Administrator Hi Eric, At Microsoft we typically use 3-4 gig per hour as a rule of thumb although alot depends on hardward, disk configuration, etc. Craig -- Craig Philbeck a-cphil@online.microsoft.com Mi...

Custom Calculation
Hi, I need to create a custom calculation using resource rate. Does anyone know the field I should use to utilize a resource std rate? Thanks, From the Resource Usage view, the field is "Standard Rate." This is not directly available on the Gantt view because Standard Rate is a Resource Table variable and the Gantt Chart uses Task type variables. Chances are, if you need to use this rate on the Gantt Chart, you are going to have to do it using VBA. -- If this post was helpful, please consider rating it. Jim Aksel, MVP Check out my blog for more informati...

Auto Calculate Monthly Time Pace
I need to show how far into a month we are at any given time in % form. I want this to change every day automatically. Example: Today is day 27 of 31 days in January - 87% if today was 2/17/10 it would need to show 17/28 or 61%, 12/20/10 would be 20/31 or 65%, and so on for each day throughout 2010 (12/20/10 would be 65%) Is there a formula or function to do this? Thanks! Heather "HeatherJ" wrote: > I need to show how far into a month we are at any given time in % form. [....] > Today is day 27 of 31 days in January - 87% One way: =day(today()) / ...

Two Condition Vlookup?
I am currently trying to create a function that searches through an array for two exact column values which allows me to retrieve a third column value. For example: Type of Fruit Date Packaged Amount of Fruit Apples June 100 Oranges June 50 Apples July 75 Grapes July 50 In this example, I would be looking to retrieve the Amount of Fruit (Apples) that was packaged in July. I would be putt...

IRR Calculations
How do you use the Function for IRR if you have an outlay of cash at the start and then you receive the same amount of cash indefinitely? Hi Laurie! If it's a single outlay: Rate = CashFlow/Outlay Rate will be the effective rate for the periodicity of the cash flow. With a monthly cash flow Annual Effective = (1+CashFlow/Outlay)^12-1 -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. You can't use the IRR function because your cash inflows do not e...

Conditional formatting if condition of an if statement is true and a string value is displayed
Dear Experts: I got a nested 'IF'-Formula in an excel cell that returns nothing if the condition is false. I would like to conditionally format this cell with grey shading if the condition is true and a string value is displayed. How can this be achieved using excel conditional formating functionality ? Help is much appreciated. Thank you very much in advance. Regards, Andreas Let's say your doing this conditional formatting in cell K17. In conditional formatting (xl2003) choose the FormulaIs: option and enter: =$K$17<>"" choose your formatt...

Query condition by date range
I'm trying to create a query to base a report from. I need it to return records within a date range that needs to be specified, as in specifying the beginning date and having the current date as the end date. A field is included in the query that has listed dates in the format mm/dd/yyyy. How would I write the criteria? I have part of it ready - [Please enter starting date:] . I know that's how you get the little question window. On Tue, 4 Dec 2007 14:51:00 -0800, silva wrote: > I'm trying to create a query to base a report from. I need it to return > records within a...

Need help with conditional formula (Excel 97)
Can anyone give me the correct syntax for the following formula: if A1 is blank, and B1 is more than zero, then display B1, otherwise display nothing (blank cell) Thank you JD =IF(AND(A1="",B1>0),B1,"") Success! -- met vriendelijke groetjes "Jake D" <JakeD@djhtend.com> schreef in bericht news:crkto4lv30u7uujv27ek8okr0us5t9oj10@4ax.com... > Can anyone give me the correct syntax for the following formula: > > if A1 is blank, and B1 is more than zero, then display B1, otherwise > display nothing (blank cell) > > Thank you > > ...

Conditional Field in Queries
Is there a way to make a field in a query conditional so that it only displays the value if the value is equal to something specifically set? Thanks. Nick Nick.Korynski@gmail.com wrote: > Is there a way to make a field in a query conditional so that it only > displays the value if the value is equal to something specifically > set? Thanks. > > Nick Explain "the value is equal to something specifically set". -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com ...

Pivot
Hi, I have a database of the following columns based on which a Pivot is created. There are various other parameters/Columns by which Sales, Margin are broken down. Each month may have hundreds of lines. The below is just a simple overview. Period Account Customer Type Country Amount Jan Sales x A TK 10 Jan Margin x A TK 3 Jan Sales y B DF 25 Jan Margin y B DF 15 Feb Mar The Pivot layout is in the following way Page : Period - This enables the user to choose the period for Sale...

How do i use Conditional formatting for 3+ conditions ?
I have a text box in a report which highlights in colour current members in a list of past and present member names. I have six types of current members that I want to highlight, but with conditional formatting I can only use 3 conditions in the members [Type] field. I have tried [Type]="Full member" Or "Life member" but the "Or" doesn't work ! Is there a way to do this ? thanks .. Roger CF is limited to 3 contitions, but you can use OR in those expressions. If you set Condition1 to Expression, you can use: ([Type] = "Full member") Or (...

Conditional null value
Wondering if anyone can suggest a way to simplify the following code, which I’m using in the OnFormat event of a report. If Not IsNull(DLookup("[ICD2]", "EntryICDs", "[EntryNum] = " & Me! [PtAcct#])) Then Me![Field192] = Replace(DLookup("[ICD2]", "EntryICDs", "[EntryNum] = " & Me![PtAcct#]), ".", " ") End If If Not IsNull(DLookup("[ICD3]", "EntryICDs", "[EntryNum] = " & Me! [PtAcct#])) Then Me![Field194] = Replace(DLookup("[ICD...

New Smartlists from SLBuilder only work for me, the builder
I have recently installed Smartlist Builder on several user's PC's and have created both new Smartlists & Excel Reports. The Excel Reports that have been deployed work fine for users with access. The Smartlists I have created (along with the downloaded from MS one's) are "visible to the user but no results are returned". So far I have given the users increasing security rights to be able to access the lists. I can log into their machines & run the reports as "sa" and my user id, they don't run under theirs even though they see the...

Correct way in Expression Builder
I tried to do a simple calculation (on a form) but fail to understand this: =[UnitPrice]*[Quantity] works, however if I use the Tables list from (below) and construct the formula it shows as: [OrderDetails].UnitPrice * [OrderDetails].Quantity and it does not work. I get #Name error When field names are written as [UnitPrice] is it not a standard practice to name them as such? Somewhere I also see [OrderDetails]![UnitPrice] notation. Why? Thanks ...

Help with Conditional formatting with Dates
Hi there, I have a spreadsheet in which I have to monitor various dates which are in different columns (i.e. (1) Date Tender Released, (2) Date Company Selected, (3) Date Draft Contract Forwarded, (4) Date Contract Signed etc...). Would if be possible to Conditional Format and have diffirent ROWs highlighted for each individual Contracts (I have 300 running contracts running) every time a date is filled in these different columns: Initial Step (1) = Yellow (Call for Tender released) Step (2) = Blue (Company Selected) Step (3) = Brown (Contract under Process) Step (4) = Green (Contract Si...

Help
Forgive me if "Conditional Sums" is not the accurate word for what I am trying to accomplish... I'm at a loss for what to call this situation. What I have is a spreadsheet totaling attendance figures for classes taught by two people (Dave and Cary). Column A lists their name, and next to it in Column B is the number of people who attended that class. What I need is a formula that will look at the sheet and report in a given cell the total for attendance for each person. I can work basic formulas like SUM, but that will give the total for the entire column, when what I n...

Feet to Meter Calculation
In my db I want to insert a room size. The sizes I wish to insert come to me in both square meters and square feet. I want to create two fields one showing square feet one showing square meters. I therefore want to be able to input data in to one field (say square feet) and have the corresponding size show in the other field (square meters) and vice versa Can this be done in a db or do I need to create a spreadsheet calculation and insert it in to the db? How is that done? Any replies greatly appreciated! Clive You have to remember that how the data is stored and how it's i...

XML Receipt and Conditions
I wanted to make a small adjustment to the receipt when a certain condition is met. If a particular word (eg. family) is typed into the comment on an item I wanted something slightly different to happen to the receipt I've just about got it, just stuck on the condition tag <IF> <CONDITION>len(Entry.Comment)</CONDITION> <THEN> I can get it using len but this makes it happen for any word, is there anyway I can make it so that it is more specific ie Entry.Comment contains '%family%' try <IF> <CONDITION>Entry.Comment like '%family%'&...

Calculating an hourly rate
Hi all, I have a quick question. I have a field in hours format and a field in currency format. I want to calculate the hourly rate for work done. For example �20 was earned in 2 hours. Easy for us to work this out but is there any way Excel can calculate this? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ By field in hours, I assume you mean time such as hh:mm format? Assume hours in A1, amount in A2, rate is then =A2/A1/24 The division by 24 is needed as t...

conditionally restricting values in excel
Hello, I want to restrict values in a particular column based on the value in other column. For eg. if column A has value int then column B should be restricted to values 1,2,3,4,5 if column A has value bool then column B should be restricted to values true and false. if column A has value string I dont want any validation to be done and the list box should not appear. Is this possible..... How do I achieve this ? Can I use macros to do this? How? Thanks Hi as a starting point: http://www.contextures.com/xlDataVal02.html >-----Original Message----- >Hello, >I want to restrict ...

Formula for calculating price
I need a one cell formula that calculates a price based on the following conditions Items costing the Contractor $50 or less are marked up 100% witha profit margin on the selling price of 50% items costing 50.01 to 100 are marked up 80% with a profit margin on selling price of 44.44% items costing over 100 are marked up 60% with a profit margin on the selling price of 37.50% -- nander ------------------------------------------------------------------------ nander's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6156 View this thread: http://www.excelforum.com/...

Conditional formating condition..?
Hi All I have 2 CF conditions associated with a cell (C14), one of which is: Formula is =OR($C$4="Fred SOMEBODY",C14<>"10:30:00") What happens when this is true isn't important/relevant. The value in the cell appears as 10:30 (formatted as custom/hh:mm) but the value in the 'formula window' is actually 10:30:00. This type of condition works fine when the cell contains a simple number (or text) but I can't get it to work for either times or dates. I have tried it with and without the inverted commas (around the time value) and have tried using 10:30 ...

Incorrect Balance calculations
I'm using Trial version of 2004 Internation edition (my copy is in shop awaiting collection). My am looking at one of my charge accounts... When I view account "Sorted by Date (Increasing)", my balances per transaction are fine and add up to what I expect. However, if I change the sorting to "Sorted by Date (Decreasing)", the individual balances make no sense - the transaction amounts and the balances don't coincide nor add up. There is some crazy pattern though... For example, in the table: Latter transaction, $C, shown balance is $D Early transaction, $A, shown ...