Bulk Editing Formulau

[djeddie]

Not sure if this is the right place to ask so here goes.

I have a report with about 6 subreports and all have the same formula in it to group teams together.

Whenever a team is added i need to go into each subreport and add it manually.

Is there a way for me to update all instances of the formula in the report so i only have to change it once?

 

[AndrewBaines]

If you're using XI, you can convert some (most) formulae to functions. Then you edit the function to change them all.
Other than that, you could do it with the SDK, but for 7 formulae, bit over the top.

Andrew Baines

http://www.pursuittechnology.co.uk

 

[djeddie]

Hi,

How do i convert a formula to a function?

 

[AndrewBaines]

Create a new formula, then on the toolbar of the formula editor, click the wizard.
Then click new | Custom Function.
Give it a name, and click 'Use Extractor'
Select the existing formula, then check the box that says 'Modify formula to use.....'
You'll then need to change the existing formulae in the subreports manually.
Bit fiddly first time round but should save you a load of time in the future.

Some formulae can't be converted - whileprintingrecords or UFLs can't be used.

Andrew Baines

http://www.pursuittechnology.co.uk

 

[djeddie]

Excellent tip

Thankyou for that!!

 

[djeddie]

Hi Andrew,

I gave it a go and i couldn't get it to work so i would like to show you what i need to be done and maybe you can shed some light.

I have 2 formaule.

A formula called {@group} and a formula called {@site}.

The {@group} formula looks at the team name field in my database and returns everything to the right hand side of the "-". For example "Team Crystal" is in Perth and appears in the database as "Perth1 CC - Team Crystal" and "Perth CC - Team Crystal"


The {@site} formula looks at the {@group} results and using a 'select case' formula to group the teams into sites. Below is an example:

select {@group}
case "Team Crystal", "Team Red": "Perth"
case "Team Blue", "Team Green": "Sydney"
case "Team Orange", "Team Yellow": "Auckland
default:""

So, whenever new teams are created i have to manually update the {@site} formula in all of my reports and their sub-reports.

To save me from updating all of the {@site} formula, should i be creating a custom function based on the {@site} or {@group} formula? and what do i do to the rest of the formulae so that it automatically picks up the changes?

Thanks in advance for your time.

Eddie

 

[AndrewBaines]

Was the custom function created OK?

Andrew Baines

http://www.pursuittechnology.co.uk

 

[lbass]

Do the sites remain constant? Is the site always present in the first part of the team name field? If so, then instead of {@site}, you could be using a formula like:

if "Perth" in {teamnamefield} then "Perth" else
if "Sydney" in {teamnamefield} then "Sydney" else//etc.

Or if the city always started the field and then was followed by a space or a number, you could avoid the hard-coding altogether, and use a formula like:

stringvar array x := split({teamnamefield}," ");
numbervar i;
numbervar j := len(x[1]);
stringvar y;
for i := 1 to j do(
if not isnumeric(x[1]) then
y := y + x[1]
);
y

Even if this doesn't work in this case, you might want to show samples of the variety in the field to see if there is some other approach like the above.

-LB

 

[djeddie]

Hi lbass,

in my database table the team name is in the format like this:

City - Team_Name

And everytime someone changed their mind about the structure of the teams Perth (for example) would be split into 2:

Perth1 - Team_A
Perth1 - Team_B
Perth2 - Team_C
etc

and then they would restructure and say "Okay, Perth will no longer be split" and then the database entries would like

Perth - Team_A
Perth - Team_B
Perth - Team_C

This is why i use the {@group} formula to extract everything to the right of the "-" so that when i report by team it works.

The second part is to group the teams by site. Thats the part that i hardcode using the "select case" formula.

I want to be able to update the select case formula once and not have to update it for every instance of {@site} in my reports/subreports.

If you like i can send you a copy of the report.

 

[djeddie]

Hi Andrew,

To answer your question, when i create the custom function it converted by {@Site} formula from this:

select {@group}
case "The Jokers", "ICU", "Vanguard": "Perth
case "Jumping the Shark", "Invincibles": Sydney

to this:

Function (stringVar v1)
select v1
case "The Jokers", "ICU", "Vanguard": "Perth
case "Jumping the Shark", "Invincibles": Sydney

What i want is to be able to add more teams to each case scenario as opposed to creating a function out of the {@group} field.

 

[AndrewBaines]

You're half way there. You now need to modify all the formulae in the subreports to use the function.
Then, when you add a new team, you modify 1 function rather than umpteen formulae.
When a new team is created, the function changes to:
Function (stringVar v1)
select v1
case "The Jokers", "ICU", "Vanguard": "Perth
case "Jumping the Shark", "Invincibles", "New Sydney Team": Sydney

If a new location were created, the function changes to:
Function (stringVar v1)
select v1
case "The Jokers", "ICU", "Vanguard": "Perth
case "Jumping the Shark", "Invincibles": Sydney
case "Edinburgh Team", "Glasgow": Scotland

Andrew Baines

http://www.pursuittechnology.co.uk

 

[lbass]

You didn't answer my questions, but your examples still show the site always starting the team name field, so there might be a solution like the one I suggested, but we would have to see more examples of the team name.

-LB

 

[djeddie]

Andrew&lbass,

I sorta understand how you are trying to help me but i think i may not be explaining myself properly so firstly i want to thankyou for your extreme patience with me.