- SAP Community
- Products and Technology
- Technology
- Technology Q&A
- Need help with string field formula
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
Need help with string field formula
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on 01-30-2008 1:37 PM
Post Author: dshallah
CA Forum: Formula
What I am trying to accomplish:
The report has item numbers and each item number has the
potential to be associated with up to three u2018binsu2019. So the fields are u2018item
numberu2019, u2018bin1, u2018bin2u2019 and u2018bin3u2019.
I tried to write a u2018if thenu2019 statement that would only show
records that had a value of less than 1 in each u2018binu2019 field. When I try to
write the statement I get a message that says u2018A string is required hereu2019 and
it highlights the number 1 in my statement. I have a feeling itu2019s because the u2018binu2019
fields are string fields and not number fieldsu2026? So I am not sure the proper
procedure to correct this. Thus help is needed and appreciated.
Here is what I wrote:
if {IC_LOC_HIST.BIN_NAME_1} < 1 then
{IC_LOC_HIST.BIN_NAME_1}
What is correct way to write a statement that will show me
the zero values in each column of bin fields?
Thanks!
- SAP Managed Tags:
- SAP Crystal Reports
Accepted Solutions (0)
Answers (15)
Answers (15)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: sliese
CA Forum: Formula
Hm, can you write out an example of the first few records?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: dshallah
CA Forum: Formula
Reply to: slieseFirst, the report shows item numbers and bin numbers. Each item should have a bin location number associated with it and each item number can have up to three bin location numbers. There are four fields total; item number, bin1, bin2 and bin3. I want to see if an item number does not have an association with a bin number. If I do it without formulas or filtering, just dragging into the report, I get some item numbers with a number in the bin1, bin2, bin3 fields and some item numbers have no value (blank fields).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: bettername
CA Forum: Formula
I guess we should really figure out what data it is that it can't convert to a number, so... create another formula:
if isnumeric({IC_LOC_HIST.BIN_NAME_1}) = false then ({IC_LOC_HIST.BIN_NAME_1}).
Run the report and post some examples of what appears in that formula field...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: dshallah
CA Forum: Formula
Reply to: betternameFirst, I tried the formula and all the fields returned with no data. Second, I am not sure what is non-numeric because all that shows up in the report are numbers. When I browse the data all I get are numbers. The data type that crystal says; type=string Length=4. Also, when I bring it into MS Access the fields are either populated with a number or are blank. What kind of "tidy-up" are you think of?Thanks again!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: sliese
CA Forum: Formula
What are the values that show up for that field if you just drag it to the report without putting it in a formula?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: bettername
CA Forum: Formula
Yeah, just use:
if isnumeric({IC_LOC_HIST.BIN_NAME_1}) = true
and tonumber({IC_LOC_HIST.BIN_NAME_1})<1 then {IC_LOC_HIST.BIN_NAME_1})
That way, you're just displaying where stuff is a valid number, and it's <1.
What kind of data are you getting that is non-numeric? It might be possible to have a "tidy-up" of the data before checking if it's a number or not...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: dshallah
CA Forum: Formula
Reply to silese: I tried both and get all data fields blank.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: dshallah
CA Forum: Formula
Reply to: betternameThanks for the 'if then' statement it certainly showed a lot of records (not all, but a lot) with the "Error - Should be a Number!" message. So that would point to the fact that there are non-numeric values in there. Is there a way to work around that fact and produce a formula that could give me the results I need? Thanks again!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: sliese
CA Forum: Formula
Hm, or maybe this:if isnumeric({IC_LOC_HIST.BIN_NAME_1}) and ToNumber({IC_LOC_HIST.BIN_NAME_1}) < 1 then{IC_LOC_HIST.BIN_NAME_1}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: sliese
CA Forum: Formula
That is probably because this field holds more than just "0"... does
it have words in it as well? Maybe you could do this.... If {IC_LOC_HIST.BIN_NAME_1} = "0" then{IC_LOC_HIST.BIN_NAME_1}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: dshallah
CA Forum: Formula
Reply to: kcheeb I tried it but it returned all data fields blank. Data that was previously there was removed.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: bettername
CA Forum: Formula
You must have a non-numeric value in there somewhere - up in the top left of the formula editor window, it'll show you the variables you've passed to the formula, which should help track down what's going on.
You could try and check that the value is a number first by using something like:
if isnull({IC_LOC_HIST.BIN_NAME_1}) = false and isnumeric({IC_LOC_HIST.BIN_NAME_1}) = false and then "Error - Should be a Number!"
else
if isnumeric({IC_LOC_HIST.BIN_NAME_1}) = true and tonumber({IC_LOC_HIST.BIN_NAME_1})<1 then {IC_LOC_HIST.BIN_NAME_1})
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: dshallah
CA Forum: Formula
Reply to :sliese I was able to create the formula without errors but when I tried to run the report it dumps me back into the formula writer and gives me the message 'The string is non-numeric' and highlights 'ToNumber({IC_LOC_HIST.BIN_NAME_1})'.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: kcheeb
CA Forum: Formula
You could also do the following comparsion in the if.
if {IC_LOC_HIST.BIN_NAME_1} <> '0' then {IC_LOC_HIST.BIN_NAME_1}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Post Author: sliese
CA Forum: Formula
If your bin fields are strings with "0" in them, you could convert them to a number first, then check if it's less than 1. Try this: if ToNumber({IC_LOC_HIST.BIN_NAME_1}) < 1 then
{IC_LOC_HIST.BIN_NAME_1}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Extend Your Crystal Reports Solutions in the DHTML Viewer With a Free Function Library in Technology Blogs by Members
- fuzzy search with multiple name fields in Technology Q&A
- Migration Cockpit Error (Condition record for pricing in sales) in Technology Q&A
- Formatted JSON Field in RAP Application in Technology Q&A
- CAP Extensibility: Exended Properties displayed in UI but missing in Requests in Technology Q&A
| User | Count |
|---|---|
| 88 | |
| 10 | |
| 10 | |
| 9 | |
| 7 | |
| 7 | |
| 6 | |
| 5 | |
| 4 | |
| 4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.