I have two rows in a db table and need to

[shortstuff2212]

I have the following two rows held in a db table
ID 18 Area ACTON Level PARISH
ID 18 Area COPPENHALL Level District Ward

I need to write a formula in which I can say print the Area name where the Level = District Ward.

Can anyone help me with this?

Thanks in advance

 

[dgillz]

Are you saying you want the fourth word in the list? Or the word immediately after "area" regardless if it is the forth word?

Please advise. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[shortstuff2212]

What I need to do is produce the following statement using a formula.

This Poll will be taken together with a Poll for the election of Councillors for the Coppenhall Ward of the Parish of Acton.

I need to get the formula to look at both rows and slot the area names into the statement in a specified order, but I don't know how to do it.

Any help would be appreciated.

 

[itkyle]

Can't you just use the Select Expert (since "Level" is a field in your table) and choose 'is equal to District Ward'? Or am I not understanding your problem correctly?

Kyle

 

[itkyle]

btw -

I'm assuming your data actually looks like this:

ID AREA LEVEL
18 Acton Parish
18 Coppenhall District Ward

 

[dgillz]

I am with kfish. Is you data one big long string, or is it in 3 separate fields as Kfish explained? Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[shortstuff2212]

to kfish

Yes the data looks like that but I can't use the select expert because I don't know what is going to be in the Level field. I need a formula to look at the level field and to make sure it is the same as the level field in the election table and then slot the area names into the statement. I have tried to do this by assigning each level to a variable and then incorporate the variable into the text field but that didn't work.

 

[dgillz]

Do you mean you need to LINK the two tables by the level field? Your post indicates these are in the same table - 2 rows in a db.

If these are in different tables, the formula would be as follows:

"This Poll will be taken together with a Poll for the election of Councillors for the "+{table.area}+" "+right({table1.level},Instrrev(" ",{Table1.level})+ of the "+{table2.level}+" of "+{table2.area}



Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[shortstuff2212]

dgillz I am not explaining this very well am I? I have the following formulas.

@Area1
// if this is a combined election type ie. it has a slash in it...
if instr({election.ElectionType},'/') > 0 then
// put the primary election type into @AreaName1
left({election.ElectionType},instr({election.ElectionType},'/')-1)
else
{election.electiontype}

@Area2
// if this is a combined election type ie. it has a slash in it...
if instr({election.ElectionType},'/') > 0 then
// put the secondary election type into @AreaName2
right({election.ElectionType},length({election.electiontype})-instr({election.ElectionType},'/'))
else
{election.electiontype}

Primary bit
if uppercase({ElectionArea.Electoral_Level})=uppercase({@Area Name1}) then
{ElectionArea.ElectionAreaName}

Secondary bit
if next({election.electionid}) = {Election.electionid} then
if uppercase(next({ElectionArea.Electoral_Level}))=uppercase({@Area Name2}) then
next({ElectionArea.ElectionAreaName})

then I use a formula to put the two together

@Primary bit +'Ward of the '+@Secondarybit +'Parish'

When I preview the report, the @Primary bit is correct but the @Secondarybit does not go past the first record.

Is this any better?

Thanks

 

[dgillz]

So you have to look at the next record? How are controlling the order the records are coming into the report?

I'd still like to know if you have two tables or one.

Regarding @Secondarybit, try using AND in your two conditions as follows:

if (next({election.electionid}) = {Election.electionid} and
uppercase(next({ElectionArea.Electoral_Level}))=uppercase({@Area Name2})) then next({ElectionArea.ElectionAreaName}) Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[shortstuff2212]

I am trying to look at the next record, but CR is having none of it. The first thing the report needs to do is go to the Election Table (1) and look in the {Election_Type} to see if it is a combined election, in this instance it is a DISTRICTWARD/PARISH election. Then it has to go to the ElectoralArea table to find the areas. In there I have two rows with the data shown above and this is where I use the formulas.

I don't understand what you mean when you say 'How are you controlling the order the records are coming into the report? Do you mean the grouping of the report?