Script Trigger to show/hide a field based on data in another field

[rt916]

Hello,

I am working on an older FMP 11 database and trying to create some new features while we wait to migrate to FMP 14. I setup a layout with some basic data collection, where some of the fields are simple YES/NO drop down selections. The data gets populated into another layout for printing and exporting, and I have a few fields where the visibility needs to be based on the result of the YES/NO response. That seems to be available with the inspector in FMP 14, but all of the users are on FMP 11 for a unknown amount of time. 

I assume this needs to be created using a script trigger on the layout, to run at OnRecordLoad.

Something like If table::record = YES, display this text result. But I am new to FMP and learning the functions.

Any help would be very much appreciated!! Thank you in advance!

[webko]

I think the Hide When... functions only became available in FM Pro 13

After that you get into different layouts for for showing/hiding (blecch!)

[Fitch]

One way to do it is with conditional formatting. Based on the results of a calculation, you can change the color of the text to match the layout background; or, you can change the size of the text to a very large number so that it's pushed outside the boundaries of the field or text box.

[webko]

Forgot about that trick

[rt916]

Hey Fitch do you have an example I can look at possibly? Not sure if I would have to change the YES/NO field to a value list, and return a 1 value with a checked box to base that calculation off. Seems like all these FMP 11 work-arounds get very... creative! Maybe I should push for the new features to be available on FMP 14 and try building them in there?

 

[comment]

Another option is to place the objects inside a portal, and make sure that a related record exists or not, based on the selection. It's not quite clear what exactly your need is: do you want to show/hide the fields when printing, or during data entry, or ... ? Your mention of exporting is especially confusing, as that has nothing to do with what's shown on the layout.

Anyway, script triggers play no role here - unless you are browsing records in Form view, and want the layout to change according to the loaded record's status.

---
BTW, I would recommend you use 1/0 (or 1/empty) for Boolean fields, instead of YES/NO - see:
http://fmforums.com/topic/47570-set-a-field-based-on-portal-content/#comment-222189

 

Edited February 21, 2017 by comment

[rt916]

we are browsing records in Form view, and want the layout to change according to the loaded record's status. the collected data gets formatted and inserted into a layout that's setup for printing and pdf export. 

I thought about doing a portal but the end-result document is size 12 font, and I don't think I can fit the portal into a box that small!?

I'll try the 1/empty suggestion, that seems more like a logical expression that won't break as easily. thank you!!  

[comment]

I am afraid I remain confused. Browsing is one thing, printing and PDF-producing is another. You cannot switch layouts during printing.

Edited February 21, 2017 by comment

[rt916]

That's my fault, not yours  It's difficult to explain some of these workflows over threads. But your advice is helping me figure it out!

I won't be switching layouts on a record-by-records basis. Only 1-2 records are affected by this issue and I think your suggestion to make them numbers will work. But, to make this even more confusing, the back end is on SQL server. So I would create/update the record in SQL as a number field right? and lose the text field. then change the field in FMP to a value list, and perform the calculation based off that. yeah?

 

[comment]

12 minutes ago, rt916 said:

So I would create/update the record in SQL as a number field right? and lose the text field

I guess. These is really a side issue, unrelated to your main question here. Whatever solution is found to that, it can be made to work with YES/NO values, too - just a bit less elegantly.

 

16 minutes ago, rt916 said:

then change the field in FMP to a value list, and perform the calculation based off that. yeah?

The value list has nothing to do with the calculation. It only helps with data entry and formats the field for display; the calculation works off the actual data in the field, and is unaffected by how the field is formatted on the layout.

 

20 minutes ago, rt916 said:

Only 1-2 records are affected by this issue and I think your suggestion to make them numbers will work.

I am not sure it matters how many you have. And as I just said, making them numbers will not solve the show/hide problem.

 

[rt916]

FYI the suggestion Fitch had did the trick! Conditional formatting was a nice work around and the simplest to setup. Thank you guys for the help, cheers!