Need to find records which are equal and not equal

[raymanj]

I need to find the records that are not equal to what ever is typed in. How do I do this?

Setup

I have an inventory.fp5 file (keeps track of all our items, general information of the items) and InvQty.fp5 file (keeps track of quatities for all items, when, how much, and what type of transaction[sold, purchased, returned, adjustments, etc]).

All searches are performed in the Inventory.fp5 file.

Main fields I want to search:

• Category (text field, located in Inventory.fp5)
  
• OnWeb (number field [0=no, 1=yes], located in the Inventoru.fp5 file)
  
• DateEntry (date field, located in the InvQty.fp5 file)
  
• TransactionType (text filed, located in the InvQty.fp5 file)
  

What I want to Find

I am trying to find all our web items that have not been sold in the last 3 months.

So I would need to enter the following search data into the fields during a search:

• Category = nothing entered, blank
  
• OnWeb = 1
  
• DateEntry = 11/25/2003...1/25/2004 (<-- today's date)
  
• TransactionType <> sold (not equal to sold)
  

Problem

How do I do the not equals in the 'TransactionType' field? I have tried 'TransactionType' = sold and selecting omit, but that seen to omit all sold items not just in the date range.

I think I need to do some kind of new find request, but not sure how. What does it mean to have a new find request anyway? Is it a "and" or a "or" search?

I tried this suggestion, from someone, already and it did not find what I needed.

Your find should be:

Category = nothing entered, blank

OnWeb = 1

DateEntry = 11/25/2003...1/25/2004 (<-- today's date)

**NEW REQUEST

OMIT

TransactionType = sold (not equal to sold)

This type of search seems to omit all 'TransactionTypes' = "sold". I need to find records in Inventory.fp5 file that have no Sold records, in InvQty.fp5 file, during my date range.

Any help is welcomed, as I have been frustated with this for a while now.

Thanks

[John Caballero]

How about capturing all those conditions in a calculation that returns a value of 1 if all the conditions are true? It would look something like this:

(IsEmpty(Category) and OnWeb=1 and DateEntry >= Status(CurrentDate)-90 and DateEntry <= Status(CurrentDate) and TransactionType <> "sold")

If all the criteria that you're testing are true, the calculation would return a value of 1. You could then search for all the records that have a 1 in the calculation.

[raymanj]

How about capturing all those conditions in a calculation that returns a value of 1 if all the conditions are true? It would look something like this:

(IsEmpty(Category) and OnWeb=1 and DateEntry >= Status(CurrentDate)-90 and DateEntry <= Status(CurrentDate) and TransactionType <> "sold")

If all the criteria that you're testing are true, the calculation would return a value of 1. You could then search for all the records that have a 1 in the calculation.

1. I need to allow the user to change their search criteria for any given search. The user may search all categories therefore leaving the field blank or they might search a particular category so they would enter a category into the field. The same is for the data range. One time they will search one month back or 6 months back they decide at the time of the search.

2. Creating a calculation, especially unstored, would be a very long search process, with 20,000 records to go through. So I don't think this would work.

Am I able to do a search with some fields equal to something and other fields that are not equal?

Thanks for the help John.

[Ugo DI LUCA]

Hi Ray,

"I need to find records in Inventory.fp5 file that have no Sold records, in InvQty.fp5 file, during my date range. "

You could try :

1. a Compound Calculation that would keyed the category, OnWeb and TransactionType fields in both the InvQty.fp5 file

c_CompoundIndex = Category&"-"&OnWeb&"-"&TransactionType

3 globals (g_Category, g_OnWeb and g_Transaction) concanated in an unstored calc.

cg_CompoundUnst = g_Category&"-"&g_OnWeb&"-"&g_TransactionType

Use these calcs for a relationship "CriteriaRel".

2. create a Date Range relationship (SmartRange or other sample you could find)

Then a calculation Case(not IsValid("CriteriaRel"::Product_ID) and not is Valid("DateRange":Product_ID), Product_ID, "")

Drop this calculation in a new layout, and keep this sole calc there

Create a global Text Field g_IdsHolder and establish a relationship named "NotValid" with g_IdsHolder on Left side and Product_Id at Right Side.

Finally, after your criterias has been entered in the global fields, a script

ShowAllRecords

GoToLayout[Empty]

CopyAllRecords

GoToLayout[where g_IdsHolder is]

Paste[select, g_IdsHolder]

GoTo Related Records [-Show Only-"NotValidRel"] *

Go To Layout [ListLayout]

* Or use a portal

HTH