| Author |
Message |
venlisa
Newbie
Joined: 23 Sep 2015
Location: United States
Online Status: Offline
Posts: 12
|
   Topic: Formula Help!!
Posted: 27 Oct 2015 at 5:55am |
|
Hi,
I am creating an operation report:
Sequence#10 - Prep - Status: completed
Sequence#20 - Work - Status: released
Sequence#30 - Inspection - Status: completed
Sequence#40 - Repair - Status: completed
Sequence#50 - Inspection - Status: released
Sequence#60 - packing/Shipping - Status: released
Let's say Seq#10, #30, and #40 were completed. Seq#20 was skipped and do not need to be done. How do I show on the report the next Seq# to be work on?? In this case, I only want the report to show Seq#50 to be work on next, which is Inspection again!
What should the query formula look like?
Thanks so much!!!
Lisa
|
|
Lisa
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Oct 2015 at 2:48am |
|
how do you know it was skipped and that it was OK to skip it?
If the assumption is that the next number in the sequence after the maximum value completed is the 'work on next' value then you have to explain your data set a little more?
do you have one table with all the sequences and completion dates to show a 'status'?
do you have two tables that you have to join?
|
|
IP Logged |
|
venlisa
Newbie
Joined: 23 Sep 2015
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 28 Oct 2015 at 3:01am |
|
I know it was skipped by looking at the work order in the database, and it was OK to skip it to the next step. The problem is the ones that skipped also has the released status on it, which means I am unable to just simply filter it.
All the data was from only one table.
The idea is: if the maximum sequence# has been completed, the one and only next record should show on the report is the "next job having a released status". e.g. #40 was the Max Seq# completed, next record should be #50. All other released type of job should be suppressed.
So is this even possible to write??
|
|
Lisa
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Oct 2015 at 3:36am |
|
it all depends on your data set.
what is your raw data like?
sounds like you should be able to
filter out anything that has specific status in it
group by the job#
sort details by the sequence number ascending
display the details in the group header to only show the first detail (or next step)
suppress details
|
|
IP Logged |
|
venlisa
Newbie
Joined: 23 Sep 2015
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 28 Oct 2015 at 4:02am |
|
There are lots of raw data, thousands of work order, Seq# can be different based on the steps of a particular job.
I tried just what you wrote, if I filter only to show release, the first detail showing is #20 (skipped one that I do not want on the report)..that's why I thought a formula is the only solution.
|
|
Lisa
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Oct 2015 at 4:05am |
|
what are all of the possible statuses?
what do they mean?
is there a definitive subset of them that show all of the 'non-completed' steps?
|
|
IP Logged |
|
venlisa
Newbie
Joined: 23 Sep 2015
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 28 Oct 2015 at 4:17am |
|
There are only two possible statuses: completed or released.
Completed means a step has been completed from the work order, no need to show on the report.
Released means either skipped or the next job to do. Don't want to show any skipped. There are lots of skipped jobs in the database still contains the release status (would be too much work to go change each and every single one in the database to complete status)
I was thinking about either the Maximum, Next, or Previous formula, but so far no luck.
|
|
Lisa
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Oct 2015 at 4:30am |
|
ahh. Your original description lead me to believe the skipped items had a better status description to be used to exclude.
1 -Do you have a sequence field that is numeric in anyway or is only in a string?
2 -can you write a command or stored procedure or sql view to use as your data source or are you limited to only the existing table?
|
|
IP Logged |
|
venlisa
Newbie
Joined: 23 Sep 2015
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 28 Oct 2015 at 5:02am |
|
1-numeric
2-I am only limited to the existing table
I wrote a formula: if previous ({Status})="Completed" then {Sequence_No}.
Now when I put that in the detail section, it will show me #50 as the highest number, the last step #60 is shown as #0. Which is what I want...
now I am trying to select only the Maximum from this formula, and it is not letting me. How do I show only the maximum number from this formula?
|
|
Lisa
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Oct 2015 at 5:24am |
|
try this.
group on the jobid (or the equivalent)
create a formula as 'CompletedOnly'
if {Status}="Completed" then {Sequence_No} else 0
do group maximum on the 'CompletedOnly' formula field.
assuming your sequence numbers are always in increments of 1 do a suppression formula on the details
max(CompletedOnly,jobid)+1 <> {Sequence_No}
Edited by DBlank - 28 Oct 2015 at 5:25am
|
|
IP Logged |
|
|
|
