AnsweredAssumed Answered

Query Based Upon Multiple Fields

Question asked by TSADesign on Jun 16, 2010
Latest reply on Jun 16, 2010 by TSADesign

Title

Query Based Upon Multiple Fields

Post

I am writing an database that will be interacted with by a client's client. The primary application for this database is that it will list all open invoices for the client's client, along with additional information that they would like to  search on that isn't available through my clients normal invoice system. Since I won't have any access to the client, I am trying to make this as simple as possible for them to query the data, without having to know how to use the Find, Extend Find, Constrain Find searches.

 

I have created a standard find that will be used to find records based upon a date range using the following fields: gDate1 (beginning date = global field) and gDate 2 (end date = global field). There is then a Flag field that has the following calculation - Case (Date of Invoice ≥ gDate1 and Date of Invoice ≤ gDate2 ; 1 ; 0 ) - this all works perfectly an I wrote a script that does a find on Flag = 1.

 

Where I'm running into problems, is that there is second field (Store Number) that they MAY want to search on. I say MAY because they may just want to do a search on a date range, they may want to do a search on store number activity, or they may want to do a search on store number activity for a specific date range.

 

I created a second Flag for store number using the same idea as above, then created a third flag if there is a date range and store number entered.

 

I was trying to manage the sorts using if and else if statements, but i'm not familiar with scripting and I can't get this to work. A version of the script is below:

If [Invoice Details::Flag 3 = 1]

    Perform Find [Restore]  {find is Flag 3: [1]}

Else

   Perform Find [Restore] {find is Flag 2: [1]}

Else

  Perform Find [Restore] {find is Flag; [1]}

End If

 

I'm sure there is an easier way to do this than with Flags - in fact I trying using Set Variable and Set Field scripts, but couldn't get those to work either. The Flags at least provide me a way of seeing if the Find is flagging the right data.

 

Thank you,

Outcomes