Using Variables in Find

[Ninja]

To allow users to quickly filter a list of invoices, I wanted to add global fields to the header of the table, which allow the user to choose values which immediately filter all invoices to just those that match the chosen criteria.

I have 3 global fields: Company_name, Invoice_date_from, Invoice_date_to

When any of these fields is modified, a script runs which sets a variable to the value selected. E.g. Set Variable [$Company_name; Value:Company_name]

The script then Performs a find where the criteria ensures that it only shows records where Company_name is equal to $Company_name

 

The issues, however, are arising when I clear the global field. It clears the variable, but performs a find on "$Company_name" rather than the blank content.

I also cannot get it to search on a date range. I had hoped the user could enter an Invoice_date_from, which would find any invoice after that date, or an Invoice_date_to, which would find and invoices before that date, or both which would find any invoices between the two dates.

However, this part doesn't work at all.

I've not used variables before and the F1 help is less than helpful on the subject. Can any suggest a solution or what I'm missing please?

 

Thanks

[bruceR]

"I have a script. It isn't working. I didn't bother to show you my script. Instead, can you please just guess what I'm doing wrong?"

That is not at all a helpful way to ask questions.

Show us the script.

[Ninja]

"I'm an angry person, who uses sarcasm to vent my frustrations at life"

Whilst I appreciate your willingness to help, your sarcasm is not so helpful. I appreciate it might have been useful to show you my script, but it refuses to copy and contains more fields than the simplified version I've detailed above (as there are 6 date fields).

If you could let me know how I can show you my script without re-typing the whole thing, I'd be more than happy.

 

Thanks

Edited June 8, 2016 by Ninja

[comment]

@Ninja

I suggest you reduce your script (perhaps in a copy of the file) to the minimum necessary to demonstrate the problem, then post it here (the script or the file).

To post a script, try printing it out to PDF, then copy from there.

Sarcasm is a very useful tool, and well deserved in this case.

BTW, setting a variable to the contents of a global field seems redundant; a global field is accessible from anywhere.

[Ninja]

Hmm, I don't seem to be able to print it to .pdf

 

Script:

Set Variable [$Company_name; Value:Company_name]

Set Variable [$Invoice_date_from; Value:Invoice_date_from]

Set Variable [$Invoice_date_to; Value:Invoice_date_to]

Perform Find [Restore] (Field:Company_name - Criteria:$Company_name) & (Field: Invoice Date - Criteria: $Invoice_date_from ... $Invoice_date_to)

 

Like I say, I've not used variables before so not sure if they're the right tool for the job (one of the reasons I didn't provide the script as it might be totally useless. Certainly seems the Invoice Date part is!)

I got the impression that I couldn't perform a find on the data stored in a global field as it wouldn't be accessible in find mode, which is why I thought I would have to copy it into variables. Might have to bump my "Intermediate" status down to "Novice"!

 

Thanks

[comment]

10 minutes ago, Ninja said:

Perform Find [Restore] (Field:Company_name - Criteria:$Company_name) & (Field: Invoice Date - Criteria: $Invoice_date_from ... $Invoice_date_to)

That's unclear; please post a screenshot of the find criteria. If the problem can be demonstrated using a single variable instead of three, please do so.

 

10 minutes ago, Ninja said:

I got the impression that I couldn't perform a find on the data stored in a global field as it wouldn't be accessible in find mode

Global fields are accessible in Find mode, but - unlike variables - you cannot use them directly in find criteria. You must do:

Enter Find Mode [  ]
Set Field [ YourTable::Targetfield; AnyTable::gGlobalfield ]
Perform Find [  ]

 

Edited June 8, 2016 by comment

[Lee Smith]

1 hour ago, Ninja said:

"I'm an angry person, who uses sarcasm to vent my frustrations at life"

Bruce was not being sarcastic, he was pointing out that you failed to provide us with enough information to help.

Lose your anger, we are all volunteers and don’t deserve it.

There are many ways to share your script. Here is the Help information on how http://fmhelp.filemaker.com/fmphelp_12/en/html/preview_print.6.6.html 

Please read this topic by Ocean West  Anatomy of a Good Topic

[Ninja]

Thank you comment. The variables plan wasn't nearly as simple as setting the find using the global fields directly. I've attached the script, which works perfectly.

My apologise to anyone offended by my earlier comments, I obviously didn't explain myself properly.

Thanks

 

[Fitch]

Your second Else If is identical to the first one, and so will never be evaluated.

I think you meant that to test whether the TO date is empty, not the FROM.

[Ninja]

You're right. Well spotted. Thanks