Display subset of found records

[aalkemist]

Hey All --

I have a search script that is working - yipee - it finds invoices within a date range and displays them

I want to add an additional parameter that only displays the records found that also match what the user selects from a Picklist ie. Web Orders, Catalog Orders, Phone Orders

Basically, FIND all invoices in a Date Range that were Phone Orders

I can't seem for the life of me to make the additional parameter work. Does anyone have a direction they can point me in?

I've attached what is working, I've removed all of the code that I can't make work - any help is greatly welcome - not sure if Modify or Omit is my best bet

 

[comment]

28 minutes ago, aalkemist said:

FIND all invoices in a Date Range that were Phone Orders

That would be something like:

Go to Layout [ Invoices ]
Enter Find Mode []
Set Field [ Invoices::Date; Invoices::gStartDate & ".." & Invoices::gEndDate ]
Set Field [ Invoices::Type; "Phone Order" ]
Perform Find []

This assumes you have 2 global date fields in the Invoices table (being global, they could be in any table) to hold the start and end dates of the wanted range.

[aalkemist]

Thank You - This helped me find the uglies in my script!!!

 

[Steve Martino]

I would add that you are checking for empty values of both fields, not one and/or the other.  What I did with my script was if the start date was empty, I auto entered the date of the oldest invoice.  If the end date was empty, I auto enter today's date.
I also check to see if the start date is before the end date.

I don't know how to post the script, but here's a snippet.

If [IsEmpty ( DELIVERY::gDate_start )]
   Set Field [DELIVERY::gDate_start; Min ( DELIVERY::Del_date )]
End If

If [IsEmpty ( DELIVERY::gDate_end )]
   Set Field [DELIVERY::gDate_end; Get ( CurrentDate )]
End If 

#Check to see if the start date is greater than the end date.

If [DELIVERY::gDate_start > DELIVERY::gDate_end]
   Show Custom Dialog [Title:"Start Date (From) must be less then End Date (to)."; Message: "Start Date (From) must be less then End Date (to)."; Default Button:“OK”, Commit:“Yes”]
   Go to Field [DELIVERY::gDate_start]
   Exit Script [Result:False]
End If

 

[bcooney]

Hey @Steve Martino there are some issues with the script you've posted. The MIN ( )  function takes more than one field, a repeating field or a related field. As written, line 2 returns the value in the current record's Delivery::Del_date, not a minimum across a list of records.

To get the "oldest" invoice, you could use a summary field that is Minimum of the target date field. That would be found set aware, though, so if the "oldest" isn't in the found set, you've returned only the "oldest" in the found set.

I'd advise you to use a cartesian self-join to Delivery with the Min() function. Or, use eSQL, but remember to commit first, so that you're not query a table with an open record.

It all depends on how you define "oldest" invoice date.

MinDemo.fmp12

[Steve Martino]

Thanks Barbara for the input.  It is a snippet based on a related table.
I don't quite know where this comes from in your post, maybe a copy/paste error? MIN ( )  function 

[bcooney]

Great. It’s not clear what context you’re in and that delivery is a related table. I rely on anchor buoy table occurrence naming. 

[Steve Martino]

1 hour ago, bcooney said:

Great. It’s not clear what context you’re in and that delivery is a related table. I rely on anchor buoy table occurrence naming. 

It was just an idea to auto enter missing data, dates.  I should've made a little sample file.

[comment]

On 11/25/2021 at 12:11 AM, Steve Martino said:

if the start date was empty, I auto entered the date of the oldest invoice.

You could just as well enter the date of Jan 1, 0001 and save yourself the trouble of finding the date of earliest invoice (which, as mentioned, is easy if you're starting from a parent table, e..g. Clients - but less so if you're in Invoices).

 

[LaRetta]

35 minutes ago, comment said:

You could just as well enter the date of Jan 1, 0001

And one could even just leave it empty, aalkemist.  FM would enter this into the Invoice date field:

...11/27/2021

Same is true by leaving end date empty; FM assumes all greater dates where field search value would be:

5/1/2021...

Note that you can still use two periods in your script as Comment indicates but FM inserts three into the field during the search.  All one needs to protect from is no dates entered which could simply be:

If [ not ( DELIVERY::gDate_start & DELIVERY::gDate_end ) ]

The reason this works is that dates always contains integers and, for boolean thus returns True. 😀

[comment]

6 minutes ago, LaRetta said:

And one could even just leave it empty

[looking for emoji of slapping forehead...]