Return Only Pertinent Data From a Table

[LizRCD]

We need a formula to enable us to eliminate the unnecessary data and just return the current work center.

Table “jodrtg” is were the data pertaining to operation number, work centers, and routing reside. In the column “foperno” the table lists all of the operations that a single work order (job number) is to go though. Adjacent to this column is one called “fnpct_comp” which indicates what percentage of the particular operation is completed at any given point in time. As you can see in the table below all of the operations in the routing are listed, whether or not any operation has been actually completed. Being interested in knowing where the work order actually is in a given point in time and not where is has been or will go through.

For example “jobno” 04471-0000 is completed since operation 80, being the last operation in this case, has been 100% completed. Thus we only need to show a piece is completed when the last operation is finished and not show the previous operations.

In the 2nd example, this particular work order has not even been started. All we need is to show that it is in the gateway-operation 20 and not where it is going to go.

In the 3rd example, operation 20 & 30 have been completed and we only need to show that this job is at operation 40.

Table jodrtg:

fjobno foperno fnpct_comp fpro_id
1 04471-0000 20 100 ASSY
04471-0000 30 100 SANDING
04471-0000 40 100 FINISH
04471-0000 50 100 CUTTING
04471-0000 70 100 UPHLOUT
04471-0000 80 100 PACK

2 04472-0000 20 0 ASSY
04472-0000 30 0 SANDING
04472-0000 40 0 FINISH
04472-0000 50 0 PACK

fjobno foperno fnpct_comp fpro_id
3 04856-0000 20 100 ASSY
04856-0000 30 100 SANDING
04856-0000 40 0 FINISH
04856-0000 52 0 CUTTING
04856-0000 70 0 UPHLOUT
04856-0000 80 0 PACK

LizS@RoyalCustomDesigns.com

 

[synapsevampire]

You have 2 conditions as I understand it.

Either the foperno is at 80 and fnpct_comp is at 100, or you want the minimum foperno where fnpct_comp < 100, sound right?

You might exercise caution in posting emails in your posts, the bots may pick it up and spam you relentlessly.

Try grouping on fjobno and going into the report->selection formulas->group and uning something like:

(
{table.foperno} = 80
and
{table.fnpct_comp} = 100
)
or
(
{table.fnpct_comp}=0
and
{table.foperno}=minimum({table.foperno},{table.fjobno})
)

I can't test right now, but this looks very close.

-k

 

[lbass]

You could try creating a formula:

//{@unfinished}:
if {table.fnpct_comp} = 0 then {table.foperno} else 9999

Then go to report->edit selection formula->GROUP and enter:

if maximum({table.fnpct_comp},{table.fjobno}) = 0 then
{table.foperno} = minimum({table.foperno},{table.fjobno}) else
if minimum({table.fnpct_comp},{table.fjobno}) = 100 then
{table.foperno} = maximum({table.foperno},{table.fjobno}) else
{table.foperno} = minimum({@unfinished},{table.fjobno})

-LB