How can I make variable that is re-defined per section and readable in same-section formula? 2

[AleXSR700]

Good morning,

unfortunately my initial approach for using a formula to identify the section the formula is iused in, was not possible with built-in methods (

https://answers.sap.com/questions/13931185/possible-to-use-if-with-referal-to-eg-details-b.html).

I now have a new approach but I seem to be getting one detail wrong.

Idea is to have a global stringvar as identifier in each section that changes value with section change. And then use this stringvar in a formula within that section.

//formula a
global stringvar section:="a"

//formula b
stringvar section:="b"

//formula test
stringvar section;
local numbervar test;

If section="a" Then
    test:=1
Else If section="b" Then
    test:=2;
test

However, the stringvar section is being shown correctly in each section but the result of test is always the same (that for the second section.

Does anybody see what I am doing wrong?

Thank you

Alex

 

[dgillz]

Global variables are not required here. Put this is section A:

WhilePrintingRecords;
Stringvar test:="A";

Stringvar test;

This in section B

WhilePrintingRecords;
Stringvar test:="B";

Stringvar test;

I don't understand your If formula, or if you still need it, but these 2 formulas will work:

@ValueSecA

WhilePrintingRecords;
If stringvar test :="A" then 1 else 2

@ValueSecB

WhilePrintingRecords;
EvaluateAfter({@ValueSecA});
If stringvar test :="A" then 1 else 2

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[AleXSR700]

@dgillz
Thank you for your response, but no, your code does not work either (or rather it does, if that were the goal ... if you adjust the if expression to "=" instead of ":=" )

But the actual issue is that your code will return "1" for @ValueSecA, no matter whether I place it in details a or details b.

The goal is to have
- one formula {a} in details a that declares the condition variable "test"
- one formula {b} in details b that changes the condition variable "test"
- one formula {if} placed in both details a and details b that checks the value of "test" and reports "1" when this formula {if} is in details a and reports "2" when {if} is in details b

So in total, 3 formulas, not 4. The formula going into details a and b must be one and the same ({if}).

 

[dgillz]

Yes I have 4 formulas. Why are trying to reset the value of the string variable rather than just returning the value 1 or 2? As opposed to "1" or "2"

Once this value 1 or 2 is returned, what are you going to do with it? How about you provide the actual desired result instead of 1 and 2? I'm pretty sure that is not your desired result.

Macola and SAP Business One Consultant o

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[AleXSR700]

I want to use one formula in details a and in details b.
Then I want that formula to select the input database field depending on whether the formula is being called in details a or in details b.

local numbervar inputdata;
stringvar section_identifier;
If section_identifier="Details_a" Then
[indent]inputdata:={field1}[/indent]
Else If section_identifier="Details_b" Then
[indent]inputdata:={field2};[/indent]

...and then I start calculations using inputdata...

That way I have only one formula in all details sections and only need to maintain one instead of many. Right now that will reduce my formulas from 20 to 10 and make life much easier.

Effectively I can then have only one formula per column instead of one per column and one per details section.

So if 10 columns and 3 details sections (rows) I have 10 formulas instead of 30. Because the only thing that changes within one column is the input database field. The rest of the code remains the same.

 

[dgillz]

Where are assigning a value to stringvar section_identifier?

And how many total possibilities are there for {input_field}?

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[AleXSR700]

Sorry, the `stringvar section_identifier` is taken from whatever formula is placed in the two sections. I did not write it down because I obviously do not know the formula.

So in your examples it would have been the variable "test".

// formula {a} placed in Details a
somehow declare section_identifier so it is only available to formulas in Details a

// formula {b} placed in Details b
somehow declare section_identifier so it is only available to formulas in Details b

// calculation formula placed in Details a and Details b. this formula is the actual output the user will then see
local numbervar inputdata;
stringvar section_identifier;
If section_identifier="Details_a" Then
[indent]inputdata:={field1}[/indent]
Else If section_identifier="Details_b" Then
[indent]inputdata:={field2};[/indent]

...and then I start calculations using inputdata...

EDIT: per formula there are currently 2 possible input fields. So IF ELSE would suffice. But I use IF ELSE IF just to show that there might be more in the future.

 

[dgillz]

So what value do you want to assign to the variable in each of the first 2 formulas?

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[AleXSR700]

Details_a" and "Details_b

 

[dgillz]

This is still way too generic for me. What are field1 snd field2 and what are you going to do with them once this formula is "working"?

Does the report have any groups?

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[AleXSR700]

:-D
What difference does it make?

In one case the difference between the two fields is a factor. One value is the raw, relative value (€/month) and the other is the absolute (€ total).
In other cases they have other data and are processed differently.

I then convert and calculate different things based on the two.

Anyhow, it makes absolutely no difference what the fields contain. The question remains: how to let the formula know in which Details section it is located?

 

[dgillz]

It makes every difference. It is very likely that we could do this with a single details section. Let's see some raw data and desired results. Also the grouping is a very critical question that you did not address.

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[AleXSR700]

Okay. I don't see how it makes any difference, but let's see.

Here the simplest of simple examples (omitting the scientific notation creation steps).

local numbervar input:=1.23;[indent]// example value for {field1}[/indent]
local stringvar final_result;
local numbervar reference:={fieldref};

If input=0 Then
[indent]final_result:=""[/indent]
Else
[indent](
If input<reference Then
[indent]final_result:="< "+reference[/indent]
Else
[indent]If {?notation}="Decimal" Then
[indent]final_result:=input[/indent]
Else
[indent]// calculate scientific value[/indent][/indent]
[/indent]
final_result

To make the code compatible with the forum I re-wrote it here, so the "else" usually is missing the ";".

In addition, I did not write out all the steps to create a scientific number (1E+01).

{field1} is the only thing that changes between Details a and Details b. And the results must not be global because I want to make absolutely sure that that result cannot be used by mistake anywhere else.

 

[dgillz]

I asked for sample data and desired results, since our dialog about specific formulas was not helping. You provided neither. And with this response you doubled down on the formula. I give up. Good luck.

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[AleXSR700]

Thank you for your help but to be honest, this discussion was going nowhere.

Sample data? Use as many different float values as you want. Result? Use any number and convert it to text.
As I said: input number, play with it, output result. Details are irrelevant.

The goal was to identify the section in which the formula is run so I can use it in an if expression. If this can be done it will be completely independent of any formula that follows.

Nothing in this thread suggests it can be done.

 

[pmax9999]

I think you a right, in that it probably can't be done.

I suspect that your approach here is wrong. Rather than persisting with an approach that almost certainly won't work, consider explaining the data and the end result you want to achieve. I share dgillz's opinion that there is probably a solution to your problem but it won't be the current approach that you seem so unwilling to abandon.

So if you want help, provide sample data, details of the report structure and the result you are trying to achieve and we might be able to assist.

If you can't do that you will get no further assistance.

 

[AleXSR700]

{Field1}=[11.2454, 22.4805, 66.32, 32.1206]
{Field2}=[23.6734, 33.567, 19.0, 101.0003]

Result ist the same value as input value but formatted either as decimal with adjusted number of decimal places toText({FieldX}, {?Decimals}) or scientific notation, i.e. 1.12454E+1. And then converted to a string.

Notation depends on user selection {?Notation}.

Whether field1 or field2 is used depends on whether the formula is placed in details a or details b.

 

[pmax9999]

It is still not clear to me what you are trying to achieve but I think the broad approach required will be to use multiple sections and conditionally suppress those you don't wish to show.

Sorry I can't be more help but it's the best I can do based on the limited information provided.

 

[AleXSR700]

I do not know how to be more clear, I'm sorry.

I am of course suppressing the sections I do not want to see. But the point is that I cannot change the input used (which alters the result) of a formula based on which section it is placed in.

So, once again:
I have one formula and two details sections. I place this one formula in BOTH details sections. When the user selects details a, details b is suppressed (and vice versa). When details a is shown, the formula shall use field1 as input. When details b is shown, the formula shall use field2 as input.

 

[pmax9999]

You continue to restate your current approach, that seems to me to have been conclusively shown to not be viable but have failed to adequately describe the actual result you are trying to achieve and the circumstances under which the results should be applied.

My final piece of advice is, instead of focussing on the section the formula is in to determine what field is shown, suppress one of the fields based on the data, parameter, or whatever ultimately is driving the required outcome.

You might have noticed there are only a small number of members that are still prepared to invest their own time to assist other Crystal Report users. If you are going to continue to seek assistance here I'd suggest you be more willing to describe the issue, the data and the solution you are trying to achieve and focus less on an approach that may not even be possible.

I for one am unlikely to bother trying to help you again based on your current attitude.