Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

Button Color based on Script results

Discussion created by BERGSTEN on Sep 28, 2017
Latest reply on Sep 28, 2017 by philmodjunk

Like • Show 0 Likes0
Comment • 15

Hi there,

I have currently scripted a couple buttons to perform a housekeeping function. Basically you click it to see if any rentals (each record = 1 rental) have not been checked in yet within a specific time frame (today's date and back). It is set to take you to a list of all the records that show a rental has not yet been checked in.

I was wondering if there was a way to have the buttons stay Green (when all rentals are checked in) and to turn red when there are rentals to investigate. Ideally the user would click into the layout and either see a green or red button based on the results of the script.

I figure I could use conditional formatting, but I don't know how to tell the script to run upon entry vs. upon clicking the button. I also don't know what the language used for the 'condition' to know to change color.

Hopefully I make sense.

Thanks

Outcomes

15 Replies

philipHPG Sep 28, 2017 7:06 AM
Conditional formatting does not use a script; you identify a condition and then configure what formatting to apply when that condition is true. When you open the Conditional Formatting dialog box, you have the option to configure different types of formatting changes (you would probably want to change the fill color) and then specify the Condition (you would probably use the Formula Is option and specify a formula that evaluates to true when you want the formatting to apply (we would need to know more about the structure of your system to advise you on what formula to use).

If you are adamant about using a script, you could have a script trigger set for OnRecordLoad that would perform a script and set a global variable and then the Conditional Formatting would reference that global variable, however I would recommend pursuing the approach first.
Like • Show 0 Likes0
Actions
BERGSTEN @ philipHPG on Sep 28, 2017 7:11 AM
Hey there.

Yes, I meant to say the button itself runs a script. I was wondering how I may use conditional formatting to change the color of the button based on whether or not the script returns results.

So if I enter the layout, ideally the script I set up to search for "unreturned rentals" would run in the background. If the result is 0 - the button stays green. If there are records found- the button would be red.
The script I talk about is to perform one function and the color of the button would simply be to determine whether or not the user needs to click it or not.

I figure there must be a formula I could use to say "if script returns 1+ records" turn fill red.
Like • Show 0 Likes0
Actions
philipHPG @ BERGSTEN on Sep 28, 2017 7:11 AM
Okay, then at the end of the script you would set a global variable and base your conditional formatting on that global variable. (In some cases it may be necessary to use Refresh Object after changing a global variable to ensure that the conditional formatting is updated.)
Like • Show 0 Likes0
Actions
BERGSTEN @ philipHPG on Sep 28, 2017 7:21 AM
Okay I see. I am not super familiar with global variables in this context-- what would something like that look like at the end of a script?
I have two buttons performing the same script but for two diff sections of our business - Audio Housekeeping and backline housekeeping. Currently they are only set to run upon clicking the button itself, not upon entry of the layout where they are placed (which Im assuming I also need to figure out).
I would need to set two global variables I expect I'm just not clear on the language of one. My searches only seem to tell me I'd use $$ but I don't really know more than that..

Here is one of the scripts:

Go To Layout ["Invoices" (Invoices) ]
Enter Find mode [Pause: off]
Set Field {Invoices::Prep_Status ; "Loaded" ]
Perform Find [ ]
Constrain found set [Restore]
Sort Records by field [Ascending ; Invoices::Load_Out_Date]
Go To Layout ["List by invoice ID - Totals" (Invoices) ]

Your help is appreciated!
Like • Show 0 Likes0
Actions
philipHPG @ BERGSTEN on Sep 28, 2017 7:28 AM
Okay, at the end of your script you could add something like:

If [ conditionToDetermineWhetherGreen ]
Set Variable [ $$ButtonColor ; "Green" ]
Else If [ conditionToDetermineWhetherRed ]
Set Variable [ $$ButtonColor; "Red" ]
End If
Refresh Object [ objectNameForYourButton ]

Then you would modify the conditional formatting on your button and set it up so that when the Formula Is $$ButtonColor = "Green" then the fill color is green and when the Formula Is $$ButtonColor = "Red" then the fill color is red. Don't forget to name your button with the object name for the Refresh Object script step.
Like • Show 0 Likes0
Actions
BERGSTEN @ philipHPG on Sep 28, 2017 7:34 AM
Okay I see!!! I'm almost out of the woods thanks to you!

As far as the condition to determine whether green or red- is there a simple way of saying the condition is ' if records are found based on criteria mentioned" within the "If" part of the script? I am not clear on how to communicate the need for records to be found. I understand how to use "if" based on a field, but not so much on a function.

I should be all set if I could just figure out that last component -thank you!
Like • Show 1 Like1
Actions
philipHPG @ BERGSTEN on Sep 28, 2017 7:38 AM
You could probably use If [ Get ( FoundCount ) ] to determine whether any records were found or not. (If no records were found, then the result of Get ( FoundCount ) is 0 which is treated as False, and if any records were found, the result is a positive number which is treated as True.)
Like • Show 0 Likes0
Actions
philmodjunk Sep 28, 2017 7:48 AM
I find myself wondering what's missing from your description of what this button does. If all it does is count records that have not been checked in and then changes color to show the script results, you don't need a button or a script--though that is an option. ExectueSQL, for example, could return the same count without use of a script. Depending on your data model, other options might also work that don't use a script.
Like • Show 0 Likes0
Actions
BERGSTEN @ philmodjunk on Sep 28, 2017 8:27 AM
It counts records but it takes the user to a list of the rentals not yet checked in based on the date- making it easy for the tech to scan what hasn't been accounted for, and to jump right to the rental(record) in question.
My understanding of the ExecuteSQL would mean it'd just show me a field with the number of records found, not take me to the list view of them. My experience with that is limited to basically none however. I do watch FMP training videos etc. but I find many times my specific goals/needs are more specific and wonky than the average tutorial will show. Learning as I go hah.

I just wanted the buttons to serve as a visual, fool-proof way of telling the tech that they need to investigate the 'found records'. The color change from green to red was the first thing I thought of to make that happen.

For whatever reason I'm still having an issue making it work though.
Like • Show 0 Likes0
Actions
philmodjunk Sep 28, 2017 8:28 AM
OK, but that ExecuteSQL could be used to control the color of the button so that you know whether to click it to pull up that list or not. Just another option that you can consider here. A script performed by OnLayoutEnter or OnRecordLoad (Depends on layout design as to which is best), could use such a query to set a variable that then controls the color set by a conditional format.
Like • Show 0 Likes0
Actions
philipHPG @ BERGSTEN on Sep 28, 2017 8:43 AM
So it sounds like you want the button's color to be different *before* they click on it as an indicator of whether they need to click on it. In that case, modifying the global variable in the button script won't help, as it's too late by the time that processes. Either you need to set the global variable in a script trigger (OnLayoutEnter or OnRecordLoad) or find another way to perform the calculation (ExecuteSQL is one option, of, if you could explain what is contained in the Constrain Found Set script step and what the relationships involved are, it might be possible to use another calculation.)
Like • Show 0 Likes0
Actions
BERGSTEN @ philmodjunk on Sep 28, 2017 9:12 AM
I see what you're saying. I wish I was more familiar with how to implement ExecuteSQL because it seems like the logical way to go (and the method I was looking for). I just don't know where to begin with it. Like I said I have limited to none experience with executeSQL so as usual I'm a bit confused.
Like • Show 0 Likes0
Actions
BERGSTEN @ philipHPG on Sep 28, 2017 9:17 AM
I see!! Yes you're right, upon entering the layout or record I wanted the button to tell the user whether or not they need to click on it.
As I mentioned to PhilModJunk I'm not really sure where to begin with ExecuteSQL in this context- but would be willing to learn/figure it out. I just get easily discouraged because some of the nuances with FMP trip me up even though it usually ends up being easier that I anticipate.

My Constrain Found Set is simply set to find records based on the invoices::load_out_date less than or equal to today's date.
(Rentals/records all have two dates- the day it goes out and the day it comes back (the one being used, load out date). When it comes back, it should be marked as "returned" -- but often times our techs forget to do it so we aren't clear what gear was actually processed and put back on the shelves vs. gear that's still out or, "loaded".)

It sounds like I need to set the script to run when you enter the invoices layout rather than trigger by hitting the button and from there figure out how to tell the button how to know what color to be?
Like • Show 0 Likes0
Actions
philipHPG @ BERGSTEN on Sep 28, 2017 12:32 PM
ExecuteSQL is a very powerful tool, but it can be difficult to get your head around.

Based on what you have described, this ExecuteSQL statement might work for you:
ExecuteSQL ( "SELECT COUNT ( Prep_Status ) FROM Invoices WHERE Prep_Status = ? AND load_out_date ≤ ?" ; "" ; ¶ ; "Loaded" ; Get ( CurrentDate ) )

However ExecuteSQL is very finicky (it is case sensitive whereas FileMaker is not case sensitive, and where FileMaker will identify that an invalid table or field referenced, ExecuteSQL will simply return a ?).

If you want to do the OnLayoutEnter script trigger you could essentially use the script you mentioned earlier, except that you would return to your current layout (instead of going to the other invoice layout) and then you would set the global variable based on the number of found records (which could then be used to control the conditional formatting, as described earlier). You might want to consider having that script open a new window, do the find and set the global variable in a new window and then close the window, so as not to affect the current found set.
Like • Show 0 Likes0
Actions
philmodjunk Sep 28, 2017 1:26 PM
You can modify that query to make it case insensitive on the status:

WHERE
Upper ( Prep_Status ) = 'LOADED'

or you could use:

WHERE
Upper ( Prep_Status ) = Upper ( ?)
Like • Show 0 Likes0
Actions

Button Color based on Script results

Outcomes

Related Content

Recommended Content