Filtering data in different columns from the same field

[NKOS13]

They have requested me to create a report that will pull as follows :
NewAssetNumber OldAssetNumber OfficeNumber Name/Desc

My problem is that the Old Asset Number & Office Number are sitting under the same field in a one table.l have tried to create a formula for both columns to try and see whether it will pick it up differently but in one there is data and the other nothing when l try running the report.Below are my formula's :

If {AMASSTO.OPTFIELD} in ["LOCNUM", "LOCNUMBER"] then {AMASSTO.VALUE}

If {AMASSTO.OPTFIELD} in ["OLDNUM", "OLDNUMBER"] then {AMASSTO.VALUE}

Please assist me if you can, l would very much appreciate your help.

 

[fisheromacse]

Are you making this as a display formula? as selection criteria?

For a Display Formula:
If ({AMASSTO.OPTFIELD} in ["LOCNUM", "LOCNUMBER"] )
OR
({AMASSTO.OPTFIELD} in ["OLDNUM", "OLDNUMBER"])
then {AMASSTO.VALUE}


For a Selection Criteria:
({AMASSTO.OPTFIELD} in ["LOCNUM", "LOCNUMBER"] )
OR
({AMASSTO.OPTFIELD} in ["OLDNUM", "OLDNUMBER"])
then {AMASSTO.VALUE}

 

[lbass]

NKOS 13, You were on the right track. Place your formulas in the detail section and insert summaries (maximum) at the group level (office number) and then suppress the detail section.

-LB

 

[NKOS13]

Thnks for the response, but below is what l would like to see pulling eg.

NewAsset# OldAsset# Office# Asset Name
Com-001 LB001 B35 Laptop
Com-002 LB002 C27 Desktop
Com-003 LJ003 F59 Tower
Com-004 YU005 B34 Tower

But my issue is that this data which is the old asset# and the Office number lies in the same field like below in my SQL Databse.Which l would like it sorted like above .

Field Name is Optional
Com-001 LB001
Com-001 B35
Com-002 LB002
Com-002 C27
Com-003 LJ003
Com-003 F59
Com-004 YU005

l appreciate your responses guys thanks but please help me again.The thing is that l don't work on crystal frequently .

 

[lbass]

Then your group belongs on {New Asset #}. Please identify the actual field names and how they relate to the formulas you showed in the first post. You would still use the same approach--I just can't tell how the fields correspond.

-LB

 

[lbass]

Yes, you could also use the next() function and then suppress the duplicate line with:

{newasset#} = previous({newasset#})

-LB