Hello, I recently started working with a database that keeps track of
design projects.
The Projects table is linked to the Design Status table. During the
lifespan of a design job, the design status progresses from "New" to
"In Progress" to "Sent to Printer" to "Complete." These values are
stored in a value list. Each time the status is updated, a new record
in inserted in the Design Status table with the current date, the
status that the user selects, and any comments they wish to add.

My problem is as follows:
We have a layout to keep track of our design jobs, and this layout
contains a pull-down control linked to the Status field in the Design
Status table. When we enter "find" mode and select "Sent to Printer,"
for example, we would like to see only the jobs whose most recent
status is "Sent to Printer." Instead, we get any job that had ever
had a status of "Sent to Printer."

How can we make sure that we limit the results to oly those jobs whose
status is currently (most recently) that which we are looking for?

Thanks so much for any help.

<dmatarazzo [at] gmail.com> schreef in bericht
news:1186775364.066717.174720 [at] e9g2000prf.googlegroups.com...
> Hello, I recently started working with a database that keeps track of
> design projects.
> The Projects table is linked to the Design Status table. During the
> lifespan of a design job, the design status progresses from "New" to
> "In Progress" to "Sent to Printer" to "Complete." These values are
> stored in a value list. Each time the status is updated, a new record
> in inserted in the Design Status table with the current date, the
> status that the user selects, and any comments they wish to add.
>
> My problem is as follows:
> We have a layout to keep track of our design jobs, and this layout
> contains a pull-down control linked to the Status field in the Design
> Status table. When we enter "find" mode and select "Sent to Printer,"
> for example, we would like to see only the jobs whose most recent
> status is "Sent to Printer." Instead, we get any job that had ever
> had a status of "Sent to Printer."
>
> How can we make sure that we limit the results to oly those jobs whose
> status is currently (most recently) that which we are looking for?
>
> Thanks so much for any help.
>

You have to create a field that will hold the date when a job was sent to
printer. You can then either search on this or sort on this. Whatever you
need.

In article <1186775364.066717.174720 [at] e9g2000prf.googlegroups.com>,
dmatarazzo [at] gmail.com wrote:

> Hello, I recently started working with a database that keeps track of
> design projects.
> The Projects table is linked to the Design Status table. During the
> lifespan of a design job, the design status progresses from "New" to
> "In Progress" to "Sent to Printer" to "Complete." These values are
> stored in a value list. Each time the status is updated, a new record
> in inserted in the Design Status table with the current date, the
> status that the user selects, and any comments they wish to add.
>
> My problem is as follows:
> We have a layout to keep track of our design jobs, and this layout
> contains a pull-down control linked to the Status field in the Design
> Status table. When we enter "find" mode and select "Sent to Printer,"
> for example, we would like to see only the jobs whose most recent
> status is "Sent to Printer." Instead, we get any job that had ever
> had a status of "Sent to Printer."
>
> How can we make sure that we limit the results to oly those jobs whose
> status is currently (most recently) that which we are looking for?
>
> Thanks so much for any help.

You'll have to add a Date field for when the "Sent to Printer" status
occurs. Then you can search for "Sent to Printer" AND dates after
mm/dd/yyyy (however far back you want to define as "most recently").

For example, to find all "Sent to Printer" records since and including
"1 August":
Enter Find mode
Set Status field to "Sent to Printer"
Set Date field to ">7/31/2007"
Perform Find


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

In article <110820071434578054%helpful_harry [at] nom.de.plume.com>, Helpful
Harry <helpful_harry [at] nom.de.plume.com> wrote:

> In article <1186775364.066717.174720 [at] e9g2000prf.googlegroups.com>,
> dmatarazzo [at] gmail.com wrote:
>
> > Hello, I recently started working with a database that keeps track of
> > design projects.
> > The Projects table is linked to the Design Status table. During the
> > lifespan of a design job, the design status progresses from "New" to
> > "In Progress" to "Sent to Printer" to "Complete." These values are
> > stored in a value list. Each time the status is updated, a new record
> > in inserted in the Design Status table with the current date, the
> > status that the user selects, and any comments they wish to add.
> >
> > My problem is as follows:
> > We have a layout to keep track of our design jobs, and this layout
> > contains a pull-down control linked to the Status field in the Design
> > Status table. When we enter "find" mode and select "Sent to Printer,"
> > for example, we would like to see only the jobs whose most recent
> > status is "Sent to Printer." Instead, we get any job that had ever
> > had a status of "Sent to Printer."
> >
> > How can we make sure that we limit the results to oly those jobs whose
> > status is currently (most recently) that which we are looking for?
> >
> > Thanks so much for any help.
>
> You'll have to add a Date field for when the "Sent to Printer" status
> occurs. Then you can search for "Sent to Printer" AND dates after
> mm/dd/yyyy (however far back you want to define as "most recently").
>
> For example, to find all "Sent to Printer" records since and including
> "1 August":
> Enter Find mode
> Set Status field to "Sent to Printer"
> Set Date field to ">7/31/2007"
> Perform Find

Opps! I forgot to add, if you're too lazy to set two fields every time
you want to find the records, then you can create a new Calculation
field that is set to "Recent Print Job" that is based on the Date and
Status fields.
eg.
RecentPrintJob Calculation, Text Result
= If ( (Status = "Sent to Printer")
and (Date > Get(CurrentDate) - 15),
"Recent Job",
"Old Job"
)

where the "- 15" will mark all "Sent to Printer" jobs within the last
two weeks (ie. newer than 15 days old) as "Recent Job".

Then just perform a Find where this field is "Recent Job".


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

> eg.
> RecentPrintJob Calculation, Text Result
> = If ( (Status = "Sent to Printer")
> and (Date > Get(CurrentDate) - 15),
> "Recent Job",
> "Old Job"
> )
>
> where the "- 15" will mark all "Sent to Printer" jobs within the last
> two weeks (ie. newer than 15 days old) as "Recent Job".
>
> Then just perform a Find where this field is "Recent Job".
>
> Helpful Harry

I used to keep forgetting about setting the "Do not Store Calculation
results... " under the Storage Options in the Field Definition. If
you don't, it uses the CurrentDate as of an old calculation.


> Hopefully helping harassed humans happily handle handiwork hardships ;o)

In article <1186835030.440688.16370 [at] q3g2000prf.googlegroups.com>,
TomMcIn <tom_mcintosh [at] shaw.ca> wrote:

> > eg.
> > RecentPrintJob Calculation, Text Result
> > = If ( (Status = "Sent to Printer")
> > and (Date > Get(CurrentDate) - 15),
> > "Recent Job",
> > "Old Job"
> > )
> >
> > where the "- 15" will mark all "Sent to Printer" jobs within the last
> > two weeks (ie. newer than 15 days old) as "Recent Job".
> >
> > Then just perform a Find where this field is "Recent Job".
>
> I used to keep forgetting about setting the "Do not Store Calculation
> results... " under the Storage Options in the Field Definition. If
> you don't, it uses the CurrentDate as of an old calculation.

True, you do need to change that option for the field above. I usually
do type that in, but was in a hurry yesterday. Thanks for posting the
reminder.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

On Aug 11, 6:18 pm, Helpful Harry <helpful_ha... [at] nom.de.plume.com>
wrote:
> In article <1186835030.440688.16... [at] q3g2000prf.googlegroups.com>,
>
>
>
> TomMcIn <tom_mcint... [at] shaw.ca> wrote:
> > > eg.
> > > RecentPrintJob Calculation, Text Result
> > > = If ( (Status = "Sent to Printer")
> > > and (Date > Get(CurrentDate) - 15),
> > > "Recent Job",
> > > "Old Job"
> > > )
>
> > > where the "- 15" will mark all "Sent to Printer" jobs within the last
> > > two weeks (ie. newer than 15 days old) as "Recent Job".
>
> > > Then just perform a Find where this field is "Recent Job".
>
> > I used to keep forgetting about setting the "Do not Store Calculation
> > results... " under the Storage Options in the Field Definition. If
> > you don't, it uses the CurrentDate as of an old calculation.
>
> True, you do need to change that option for the field above. I usually
> do type that in, but was in a hurry yesterday. Thanks for posting the
> reminder.
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)


Thanks for all the help; I'm learning a lot. The status table does
have ID and date fields, so I'm sure I can figure out how to sort by
them. Just in case anyone is still paying attention to my silly
little problem, though, would it be possible to set a calculation that
limits results to the very last record? For instance, if I create a
script to pull out all jobs whose most recent status is either "To
Printer" or "Complete," how would I go about doing that?

Thanks so much,

Dan

In article <1187025357.803393.24340 [at] d55g2000hsg.googlegroups.com>,
dmatarazzo [at] gmail.com wrote:

> On Aug 11, 6:18 pm, Helpful Harry <helpful_ha... [at] nom.de.plume.com>
> wrote:
> > In article <1186835030.440688.16... [at] q3g2000prf.googlegroups.com>,
> > TomMcIn <tom_mcint... [at] shaw.ca> wrote:
> > > > eg.
> > > > RecentPrintJob Calculation, Text Result
> > > > = If ( (Status = "Sent to Printer")
> > > > and (Date > Get(CurrentDate) - 15),
> > > > "Recent Job",
> > > > "Old Job"
> > > > )
> >
> > > > where the "- 15" will mark all "Sent to Printer" jobs within the last
> > > > two weeks (ie. newer than 15 days old) as "Recent Job".
> >
> > > > Then just perform a Find where this field is "Recent Job".
> >
> > > I used to keep forgetting about setting the "Do not Store Calculation
> > > results... " under the Storage Options in the Field Definition. If
> > > you don't, it uses the CurrentDate as of an old calculation.
> >
> > True, you do need to change that option for the field above. I usually
> > do type that in, but was in a hurry yesterday. Thanks for posting the
> > reminder.
>
> Thanks for all the help; I'm learning a lot. The status table does
> have ID and date fields, so I'm sure I can figure out how to sort by
> them. Just in case anyone is still paying attention to my silly
> little problem, though, would it be possible to set a calculation that
> limits results to the very last record? For instance, if I create a
> script to pull out all jobs whose most recent status is either "To
> Printer" or "Complete," how would I go about doing that?
>
> Thanks so much,

There's really only two ways to achieve this: via a Relationship or
using a script. Using a Relationship that sorts in reverse order by
Date you can easily access the newest Job record, but it's difficult to
say exactly how since we don't know the design of your database.

As an example which I'm guessing is similar to your database, let's say
you have a Task table that is linked by a Relationship to a Jobs table
- each Task has multiple Jobs associated with it. Each Job has a Date,
so it's easy enough to create a second Relationship that links the Task
to the Jobs, but this time sorted by Date in reverse order, which means
the newest Job is always first in the Portal / related records.

Now you can create a new Calculation field in the Tasks table that is
set to "Yes" (when the most recent job is "To Printer" or "Complete")
or "No" (when the most recent Job isn't one of those two).

You can use Related fields in the Calculation and FileMaker will only
use the value from the first related record (the exception being
functions like Sum, Max, etc. that summarise all the related records),
and since the first related record using this new Relationship is
always the newest by Date, it is the one you're interested in.
ie.
NewestJob_is_Print_or_Comp Calculation, Unstored, Text Result
= Case (
ReverseDateRelationship::JobStatus = "To Printer", "Yes",
ReverseDateRelationship::JobStatus = "Complete", "Yes",
"No"
)

Now you can perform a search in the Tasks table for records where
NewestJob_is_Prin_or_Comp is "Yes" and FileMaker will return all the
records with the newest related Job record that is either "To Printer"
or "Complete".

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)