Hi,

I want to show all records which meet my select expert criteria:
{JobOrder.JobType} = "Contracting" and
({JobOrder.EndDate} in WeekToDateFromSun or
{JobOrder.StartDate} in WeekToDateFromSun or
{JobOrder.FilledDate} in WeekToDateFromSun or
{JobOrder.OrderDate}in WeekToDateFromSun)

NB. There are 164

I then want the report to indicate which of those records have "Order Taken" or "Order Taken - Electrical" in the Description field from the 2nd table.
NB. There are 157

I do not want this to limit the total results. (1 if comment exists and 0 if not would be great)

When I use Select expert with the following formula it only returns the 157 with the comment.

{JobComments.Description} in ["Order Taken", "Order Taken - Electrical"]

I want something like the below to happen:
if({JobComments.Description} in ["Order Taken", "Order Taken - PRS"])exists then "yes" else "no"

i.e. 164 results 157 with Yes in the field and 7 with No in the field.

Any help would be greatly appreciated.

Thank you very much for your prompt response however I don't think I was clear enough previously.

There are many other "JobComments.Description"'s i.e. "Phone call", "Quote" etc

If I use the formula suggested it will bring up only the results which have no "JobComments.Description" or one of "Order Taken", "Order Taken - Electrical".

What I want is every one of the results which meet the initial criteria:
{JobOrder.JobType} = "Contracting" and
({JobOrder.EndDate} in WeekToDateFromSun or
{JobOrder.StartDate} in WeekToDateFromSun or
{JobOrder.FilledDate} in WeekToDateFromSun or
{JobOrder.OrderDate}in WeekToDateFromSun)

And in a field on that line it to indicate if either of the comments "Order Taken", "Order Taken - Electrical" is there. If the comments aren't there I would like the result to still show up but with an indication that neither of the comments are there (this could be a blank field or a no).

I hope this is a little clearer about the data I am working with.

Thanks again your answer seems to be what I am intending however I cant seem to get it to work.

I am putting the formula into Saved Data Selection Formula of the JobComments.Description field (is this right?).

When I try to confirm it or check the formula I get the error message:
"This function can not be used as it must be evaluated later" on the following section: PreviousIsNull({JobOrder.JobOrderNo})

If I try putting the formula in Select Expert Group it says "A number is required here" and the cursor is at the end after comments.

Also when you say IT may need to go in the group footer do you mean the description field or formula?

Thanks again for taking the time to help me with this

Still struggling so thought I would start again and try to be more clear.

I am working in FastTrack which has a number of Tables. The ones I need for this part are Job Order and Job Order Comments.

What I am trying to do:

I am creating a report which needs to show all records which meet my Job.Order selection criteria:

{JobOrder.PositionType} = "Temp" and
({JobOrder.OrderDate} in Last7Days or
{JobOrder.FilledDate} in Last7Days or
{JobOrder.StartDate} in Last7Days or
{JobOrder.EndDate} in Last7Days)

i.e.
OwnerStaffID     JobOrderNo     OfficeCode     JobStatus     JobType     CommissionType
KATH     200,037,625     100     Filled     Firm Order     Commission 1
JAMESM     200,037,562     100     Filled     Firm Order     Commission 1
JAMESM     200,037,082     100     Closed     Firm Order     Commission 1
JAMESM     200,037,549     100     Filled     Firm Order     Commission 1
CHRISTI     200,037,530     200     Filled     Firm Order     Commission 1


Say there are 100 results. I can do this successfully. The field "Job.Order.OrderNumber" is unique to each of the 100 records.

What I want to do is integrate the Job Order Comments table to return my 100 results (and only those 100 results) with two new fields which say yes or no dependent on if the JobOrderComments table has the comments "Order Taken", "Order Taken PRS" etc and/or "Order Filled", "Order Filled Fenton" for each of the JobOrder results ie each JobOrder.OrderNumber.

NB for every Job Order there are a number of corresponding comments i.e. lots of comments for each JobOrder.OrderNumber. So if I simply add the field "description" I get the following:

OwnerStaffID     JobOrderNo     OfficeCode     JobStatus     CommissionType     Description
FENTON     200,027,865     300     Filled     Commission 1     Order Taken
FENTON     200,027,865     300     Filled     Commission 1     Purchase Order
FENTON     200,027,865     300     Filled     Commission 1     Order Confirmed
FENTON     200,027,865     300     Filled     Commission 1     Order Confirmed
FENTON     200,027,865     300     Filled     Commission 1     Order Confirmed
FENTON     200,027,865     300     Filled     Commission 1     Order Confirmed
FENTON     200,027,865     300     Filled     Commission 1     Rate Authorisation-
FENTON     200,027,865     300     Filled     Commission 1     QC Closing Note
FENTON     200,027,865     300     Filled     Commission 1     Rate - Authorisation
FENTON     200,029,211     300     Closed     Commission 1     Order Taken
FENTON     200,029,211     300     Closed     Commission 1     Email/Post
FENTON     200,029,211     300     Closed     Commission 1     3. Resume only
FENTON     200,029,211     300     Closed     Commission 1     3. Resume only
FENTON     200,029,211     300     Closed     Commission 1     3. Resume only
FENTON     200,029,211     300     Closed     Commission 1     Order Confirmed - Fenton
FENTON     200,029,211     300     Closed     Commission 1     Job Order update
FENTON     200,029,211     300     Closed     Commission 1     Job Order update
FENTON     200,029,211     300     Closed     Commission 1     Order Confirmed - Fenton
FENTON     200,029,211     300     Closed     Commission 1     Job Order update
FENTON     200,029,211     300     Closed     Commission 1     QC Closing Note
                         



Visual what I want would look something like this:

OwnerStaffID     JobOrderNo     OfficeCode     JobStatus     CommissionType     Order Taken Comment Present     Order Confirmed Comment Present
FENTON     200,027,865     300     Filled     Commission 1     Yes     Yes
FENTON     200,029,211     300     Closed     Commission 1     Yes     Yes
VICKI     200,032,866     200     Closed     Commission 1     Yes     Yes
AARON     200,033,741     200     Closed     Commission 1     No     Yes
AARON     200,034,131     200     Closed     Commission 1     Yes     Yes
DEBBIE     200,034,336     200     Closed     Commission 1     Yes     Yes
TRA.     200,034,788     300     Closed     Commission 1     Yes     No
VICKI     200,034,840     200     Filled     Commission 1     Yes     Yes
FENTON     200,035,088     300     Closed     Commission 1     Yes     Yes
SALLY     200,035,483     200     Cancelled     None           No     Yes
BENNY     200,035,956     300     F-Competitor     None           Yes     Yes
KATH     200,035,960     100     Submitted     None           Yes     Yes
PEOPLE     200,036,025     300     Filled     Commission 1     Yes     Yes
BENNY     200,036,066     300     F-Competitor     None           Yes     Yes
VICKI     200,036,168     200     Cancelled     Commission 1     Yes     Yes
TRA.     200,036,233     300     Filled     Commission 1     No     No
FENTON     200,036,276     300     Filled     Commission 1     Yes     Yes
ELIZA     200,036,294     100     Cancelled     None           Yes     No
JAMESM     200,036,321     100     Filled     Commission 1     Yes     No
CHRISTI     200,036,358     200     Filled     Commission 1     Yes     Yes
CHRISTI     200,036,376     200     F-Competitor     None           Yes     No
NETWORK     200,036,445     300     Closed     Commission 1     No     No
FENTON     200,036,457     300     Closed     Commission 1     Yes     Yes
KYML     200,036,560     200     Closed     Commission 1     Yes     Yes
JAMESM     200,036,730     100     Closed     Commission 1     Yes     Yes
SALLY     200,036,742     200     Submitted     None           Yes     Yes
GAVIN     200,036,753     200     Filled     Commission 1     Yes     No
KYML     200,036,787     200     Submitted     Commission 1     No     Yes
DEBBIE     200,036,792     200     Closed     Commission 1     Yes     No
ELIZA     200,036,820     100     Filled     None           Yes     Yes
KATH     200,036,837     100     Filled     Commission 1     Yes     Yes
FENTON     200,036,855     300     Filled     Commission 1     Yes     Yes
KYML     200,036,874     200     Closed     Commission 1     Yes     Yes
VICKI     200,036,890     200     Filled     Commission 1     Yes     Yes
ELIZA     200,036,940     200     Closed     Commission 1     No     No

Returning my 100 records and indicated if there is a comment in the record in the database.

Thank you for you patience hilfy!

I think I have managed the above and that I am finally getting somewhere......

I inserted the above formula in the Formula Fields in Field Explorer. I then dragged it to the end of the data in the group footer (where i want the yes/no to appear).

The results however are still not correct.

There are 253 results that should and do come up.

There are 245 Job Orders with the "Order Taken" description in the comments(checked using excel and exporting all comments).

However 20 do not have a "yes" or "no" (and they all have the comment description "Order Taken" except 1).

Also 140 come up with "no" (most of these do have the comment) and 93 come up with "yes". The 8 records missing the comment either have "yes","no" or nothing in the formula field.