Jump to content

Recommended Posts

Hello everyone,

I have a Preference table with status options. The user can create a new status and through a script can assign a color (see attachment). The calculation is the following: 

Allow user abort [off]

Set error capture [on]

Set variable [$start ;  value: Get ( ActiveSelectionStart )]

Set variable [$end ;  value: Get ( ActiveSelectionStart ) + Get ( ActiveSelectionSize ) -1 ]

Insert Calculated Result [ TextColor ( Middle ( Get ( ActiveFieldContents ) ;  Get ( ActiveSelectionStart ) ; Get ( ActiveSelectionSize) ) ; Get ( ScriptParameter ) ) ]

Set Selection [ StartPosition : $start ; End Position : Send$ ]

The ScriptParameter has the color values in RGB: RGB ( 237; 41; 190).

The Status field is pulled into a Value List for the Product table. Is it possible, once the status is used in the Product table , to convert the text color into a conditional formatting? Is my approach wrong? I am trying to give the user some flexibility.

Thank you for guiding me.

Screen Shot 2020-03-28 at 3.51.09 AM.png

Screen Shot 2020-03-28 at 3.50.42 AM.png

Link to post
Share on other sites

Not sure I understand what exactly is your question. The colors used in conditional formatting must be preset in advance. They cannot be calculated or set by script. 

If - as it seems from the screenshots - you want to allow users to pick a color from a fixed selection of preset colors and assign it to a product code, you could store the selection in your preference table as a simple number (from 1 to 8, in your example). Then you can define 8 rules for your conditional formatting along the lines of:

Preferences::ProductCodeColorNumber = 1

and assign one of the preset colors to each rule.

Note that this requires a relationship between Products and Preferences matching Status to ProductCode.


Edited by comment
Link to post
Share on other sites

Thank you Comment,

Sorry, I lost you at "store a selection in your preference table". As per now, each color dot on the Preference table is a button. 

Do I need to change that and create a number field named "CodeColorNumber" with a repetition of 8 or maybe create 8 number fields, each one with a different value? How could I assign a visual cue to a number field?

For the second part I understand the relationships between Products and Preferences with the matching Status - Color Code. 

Thank you

Link to post
Share on other sites
34 minutes ago, PatrickDes said:

As per now, each color dot on the Preference table is a button. 

it would still be a button. But it would simply do:

Set Field [ Preferences::ProductCodeColorNumber ; 1 ]

or another number between 1 and 8.


34 minutes ago, PatrickDes said:

Do I need to change that and create a number field named "CodeColorNumber" with a repetition of 8 or maybe create 8 number fields, each one with a different value?

No. Each product code stores a single color number, so you only need a simple Number field, with no repetitions.


34 minutes ago, PatrickDes said:

How could I assign a visual cue to a number field?

The number field needs no visual cues. It doesn't even need to be shown. You can apply conditional formatting to the ProductCode field, using the same rules and same colors as for the Status field in Products.

Note: This is all assuming you want to use conditional formatting. If you prefer, you could store the RGB values in the Preferences table and apply them by auto-entering a calculated value into the Status field using TextColor(). But then changing the colors in Preferences becomes more difficult to apply to existing records in Products.


Edited by comment
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Similar Content

    • By John Link
      I have a field named "Databases" with the following values: "CORD", "SDHS", "Y Ballet". This field is displayed as a checkbox set. I recently learned that "CORD" should have been "DSA", so I naively changed "CORD" to "DSA" in the value list but then none of the records that were checked for "CORD" were checked for "DSA", which reminded me that the values in the value list are the values of the field Databases and not labels for checkboxes. I revised the value list to again include "CORD" and its checkbox is again checked for all the records for which it was originally checked.

      I wrote a script, shown in one of the attachments, to insert "DSA" into the Databases field for each of the records that contains "CORD" in that field. When I run the script I get an error message saying "Databases" is defined to contain only specific values, and that I must enter a valid value. How can DSA not be a valid value when it is included in the value list for the field? How can I accomplish what I want to do?

      Does programming scripts in FileMaker ever get easier? I don't write many scripts, but when I do I find it surprisingly difficult to accomplish what I think would be the simplest of tasks.

      Thanks in advance for your help,
      John Link

    • By Tony Diaz
      How do I set a field with a value list item from a script, from a set of tick box values? .. or remove a value list item, but leave whatever else is there alone?
      Looks like GetValue with the name of the value list and the position # of the value, but I'm sure that also means that if I change that value list, I need to change any scripts that reference them by position # too..
      Something like detecting if a string is already there, and if not add it, surrounded by C/Rs, unless it's the first item entry?
      For the first time a field is set, I presume I could just Set Field (field ; "Value List Item"), and if there's only one entry/line in it, just clear it.  But if there's already a value there, I just want to select an additional one.
    • By emncwundy
      I have two iPads connected to FM server. For some reason one ipad (5th Gen running 13.5) will not complete the navigation scripts and shows error messages (no records match this find criteria), however the 7th Gen running on 13.1.1 works fine..... Issue has only begun after purchasing the second, newer iPad. I cannot for the life of me work out what is wrong, or how to troubleshoot and would love some guidance please!! Here is a screengrab of one of the scripts and related layout:
      fm go script issue.tiff Orders iPad layout.tiff
    • By Agnes Riley
      I'm getting "The file "" could not be opened. Either the host is unavailable, or the file is not available on that host."
      This happens when a script runs. Not a new server, no new files. The file is actually open in the background. The file reference is just "file:".
      I restarted the server. Didn't fix it.
      It's apparently intermittent. I copied all the script steps to a new script, then it worked 3 times, but couldn't find the file for the 4th time.
      Any ideas?
    • By nsdodgers
      Hello, I have a number field where I want the user to be able to enter things like:
      42 (only item 42) 12+82 (only items 12 and 82) 1-5 (items 1 through 5) 31,33,35,37 (only items 31,33,35,37) So I am need of a way for filemaker to deal with the operators '+' '-' and ','
      Do you have any advice on how I can separate out the item numbers?

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.