| Author |
Message |
mariagus
Newbie
Joined: 27 Mar 2011
Location: Spain
Online Status: Offline
Posts: 5
|
  
Topic: insert a parameter on a formula
Posted: 27 Mar 2011 at 11:53pm |
|
Hi all,
I'm trying to add a new formula on my report, but I'm not sure if I'm doing ok.
I have on my db a period table which has as many periods as the user wants to add to his year.
On th other hand I have another table with some amounts related to each period. These amounts are the values that I want to show on my report.
First I created this formula:
If{periodNumber} = "001" Then {actualPeriod001}
Else If{periodNumber} = "002" Then {actualPeriod002}
Else If{periodNumber} = "003" Then {actualPeriod003}
...
Else If{periodNumber} = "012" Then {actualPeriod012}
But I need something more dynamic, and not to write again this formula for each user if they create years with different number of periods.
My idea was to I created a new parameter {?periodNumber} and add it to my formula {actualPeriod{?periodNumber}}
But this doesn't work
Has anyone an idea about how can I fix my issue?
Many thanks in advance
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 28 Mar 2011 at 3:27am |
hmm...tought one. The table design is the 'big' problem, it would seem. What if someone wants to have 50 periods in a year (basically 1 a week)...I know, why would anyone want that, but the table is only set up for x periods, and each column has a different name.
one would have thought a table layout like:
period, amount, ... would have been more flexible, for both data storage and reporting.
if you just add the parameter Period, so that the report user can enter a period that they desire, the formula would look something like:
If{periodNumber} = "001" and {?periodNumber} = "001" Then {actualPeriod001}
Else If{periodNumber} = "002" and {?periodNumber} = "002" Then {actualPeriod002}
Else If{periodNumber} = "003" and {?periodNumber} = "0031" Then {actualPeriod003}
...
Else If{periodNumber} = "012" and {?periodNumber} = "012" Then {actualPeriod012}
|
| IP Logged |
|
Keikoku
Senior Member
Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
|
Posted: 28 Mar 2011 at 3:29am |
{actualPeriod{?periodNumber}}
Aside from design issues, this appears to be a syntax error. You can't concatenate two values like that.
Try this:
If {?periodNumber} then
{actualPeriod & {?periodNumber}}
Make sure periodNumber is a string. Otherwise you will have to convert it to a string before concat'ing them.
In fact, you can simply write
{actualPeriod & {?periodNumber}}
because it seems like the only reason you're checking the period number is because you don't know which one you (or the client) want. Here, you KNOW which one you want.
This should make it a lot more efficient. Edited by Keikoku - 28 Mar 2011 at 3:32am
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 28 Mar 2011 at 3:49am |
|
nice trick...hadn't thought of that, but does exactly what my change did.
|
| IP Logged |
|
Keikoku
Senior Member
Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
|
Posted: 28 Mar 2011 at 3:57am |
|
Almost. The end result is the same, but I believe there should be a significant difference in performance since you don't have to go through say 50 condition checks for each record (worst-case scenario for a table with 50 different periods and user input happened to be the last one on the list).
However your solution is more flexible. Mine is only for the particular situation.
Edited by Keikoku - 28 Mar 2011 at 3:59am
|
| IP Logged |
|
mariagus
Newbie
Joined: 27 Mar 2011
Location: Spain
Online Status: Offline
Posts: 5
|
Posted: 28 Mar 2011 at 9:25pm |
|
First of all thanks for your quick replay :) and although the idea is great, I did't get a successful result. Maybe the sintax is not totally correct. Could you please take a look again?
If I create this formula:
{codaBudget__c.ActualPeriod001__c}
I get the result that I want, but, if I do:
{codaBudget__c.ActualPeriod & {?periodNumber} & "__c"}
I get an error: This field name is unknown.
I also tried
stringVar fieldName = "codaBudget__c.ActualPeriod" & {?periodNumber} & "__c";
{fieldName}
but the result is exactly the same as before.
Any other idea?
Many thanks in advance
|
| IP Logged |
|
Keikoku
Senior Member
Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
|
Posted: 29 Mar 2011 at 3:02am |
|
Oh, then I guess you'll just have to go with lockwelle's idea.
Crystal doesn't let you build strings and specify them as crystal fields. Probably how it's designed.
|
| IP Logged |
|
mariagus
Newbie
Joined: 27 Mar 2011
Location: Spain
Online Status: Offline
Posts: 5
|
Posted: 29 Mar 2011 at 5:48am |
|
oohhh This is the answer I didn't want to read ... it's a pity, but anyway thanks :)
Regarding lockwelle's solution, which is a good idea btw, I have another question.
This is great if I know how many periods has the customer, but my idea was to create something more dynamic because I can find that one customer has only 12 periods, while another needs 50.
How can I generate a general formula for both?
Because if I create this formula:
If{periodNumber} = "001" and {?periodNumber} = "001" Then {actualPeriod001}
Else If{periodNumber} = "002" and {?periodNumber} = "002" Then {actualPeriod002}
Else If{periodNumber} = "003" and {?periodNumber} = "0031" Then {actualPeriod003}
...
Else If{periodNumber} = "012" and {?periodNumber} = "012" Then {actualPeriod012}
...
Else If{periodNumber} = "050" and {?periodNumber} = "050" Then {actualPeriod050}
If my customer has only 12 periods, I will get "actualperiod050" is an unknown field as an error.
Is there any kind of loop that can give me this information?
|
| IP Logged |
|
Keikoku
Senior Member
Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
|
Posted: 29 Mar 2011 at 7:46am |
I don't think a loop would work because the variable would be the period number, and we've found that you have no way to specify {actualperiod(NUMBER)}
Otherwise you would've finished this already! [IMG]smileys/smiley36.gif" align="middle" />
But if you are able to determine the number of periods a person has beforehand, I suppose you may be able to say "ok this person has no more periods so let's stop"
A very inefficient method would be
if periodnum = 001 and numPeriods <= {maxPeriods} then
...
else if periodnum = 002 and numPeriods <= {maxperiods} then
...
But it would WORK to say the least. Edited by Keikoku - 29 Mar 2011 at 7:54am
|
| IP Logged |
|
mariagus
Newbie
Joined: 27 Mar 2011
Location: Spain
Online Status: Offline
Posts: 5
|
Posted: 29 Mar 2011 at 8:43pm |
|
Thank you very much.
I'm quite new in Crystal and I didn't want to skip any option that could work and make the code easier :)
|
| IP Logged |
|
|
|
