Display Data in field based on table code of another table

[wld1957]

We enter currency amounts in a table based on a code (sys_trac.actioncode). I can get all of the code and amounts in a report as follows:

actioncode trackamt
SFPP $50.00
SFX $1,170.00
SFM $1.50
SFAD $40.00
SFOA $9.00

What I have been trying (without any luck) is to take the field sys_trackamt and have it display an amount based on whatever actioncode is put in for that field. I want to put more than one on the same report but can only get it to select one actioncode. (this is from select expert for the report). I have not figured out how to diplay the amount for differnt codes if I put more than one sys_trackamt on the report.

I want to use the same field on the report several times and and have it display the amount based on a code. If it does not match that code then it would be blank or zero.

Any help or ideas would be greatly appreciated.

 

[lbass]

What do you mean by:

have it display an amount based on whatever actioncode is put in for that field.

You need to show some sample data and indicate where you want the amount to appear. If you want it to appear based on a matching code in another table, then link this table to that table on the code field, and place the amount field on the report.

If you want to show only a selection of values, then use the record selection to limit to that set. Use "is one of" to select multiple values.

-LB

 

[wld1957]

I do not know if this is even possible. Let me try and explain better what I'm trying to do. This is not a list report, it is like a form. On the form I have a field sys_trackamt.

I have used "is one of" and "starts with" to pull up a list of the data for that field. With this the field is only on the report once but when the report is run is will display a list of what is in the selection criteria. This is not what I want.

I want to put the same field on the report let's say five (5) times. Is is possible in maybe THE display string to have a formula to have it only display when it is equal to a certain code.
Example:
sys_trackamt-Format field-display string? sys_track = "SFM"
sys_trackamt-Format field-display string? sys_track = "SFA"
sys_trackamt-Format field-display string? sys_track = "SFX"
ETC..
The results are not based on the record criteria but on what the formula would be for that field in the record. The only way I have found to get around this is to make subreport for each one and then place them on the main report.

The following data is what I'm trying to extract but not as a list from one field but from each field separately.
actioncode trackamt-This same field on the report 5 times.
SFPP $50.00
SFX $1,170.00
SFM $1.50
SFAD $40.00
SFOA $9.00

Like I said, I do not know if this is possible but thought I would ask.

 

[lbass]

This is still not clear. This is the key question:

What in the main report tells you what value you want from this table?

You should be linking to another table, or you can even hard code this in a formula using select case or an if/then format.

-LB

 

[wld1957]

The value of sys_tracamt is based on the code from sys_actioncode.


This is the criteria for the main record. This allows me to bring up all the records (sys_tracamt)in the table with a code of "SF.

{cpmain.cpmainid} = {?Civil ID} and
{sys_trac.actioncode} startswith "SF"

I wanted to try and have the selection criteria within the actual field itself (sys_tracamt) so that I could use it more than once on the same report.

 

[lbass]

I don't know what this means.

I wanted to try and have the selection criteria within the actual field itself (sys_tracamt) so that I could use it more than once on the same report.

I think there is probably a simple solution for what you are trying to do, but I can't follow what it is you are trying to do. What you appear to have is a look up table. Ordinarily, there would be a main table with results something like this:

Main Table:
Dept ActionCode
ER SFPP
Admin SFX
Clinic SFM

You would then link {Main.ActionCode} to {Yourtable.ActionCode}. You then place the sys_tracamt on the report and it will display like this:

Dept Sys_tracamt
ER 50.00
Admin 1170.00
Clinic 1.50

It might be better if you laid out a sample of how you want your report to look, and also identify the tables and fields you are working with.

-LB

 

[wld1957]

There is only one (1) table. I have included the main parts of the table below.

track_id fkeyname fkeyid actioncode trackamt

CP121811 CP 121811 SFM $1.50
CP121811 CP 121811 SFAD $40.00
CP121811 CP 121811 SFOA $9.00

If I place trackamt on the report three (3) times. I want to show all three of the above numbers in the report in different areas with background text. (not in a list as is shown above).

 

[lbass]

WHERE you do want to show them? You can set up conditional formulas like:

//{@SFM}:
if {table.actioncode} = "SFM" then
{table.trackamt}

Place formulas like these in the detail section, and then you can insert maximums on them at the group level so they can be placed in the group sections, or at the grand total level. Or you can create summary formulas like:

maximum({@SFM})

For the report header or footer, or

maximum({@SFM},{table.groupfield})

If you need to use these in text boxes, then you can wrap the formulas in totext().

-LB

 

[wld1957]

Thanks, that makes sense, I tried it but keep getting a zero amount. It does give me something to keep working on though. Thanks much.