Jump to content

What is the best way to perform business logic after field update?

This topic is 2037 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi All,


I am fairly new to FM, but am an experienced developer of .net applications and have used access in the past also.


I am building a form that will need some business logic. Basically, upon update of field A or field B I need to create logic that checks that both field A and B are populated. If they are both populated I will then need to take both those values and execute a query (using A and B values in the where clause) to return a value. That value I will then display on my form.


Ok so in C#, F#, VBA or SQL I could knock that up in a matter of minutes; but In FM The solution is not readily apparent. I can see a trigger point to execute script but wanted to run by the pro's to see what mechanisms might be most efficient. 


Any advice is greatly appreciated.


Many thanks



Link to post
Share on other sites

Filemaker (when used properly) is much more "declarative" in its paradigm than "imperative". So usually you would create a relationship to automatically flow the matching data once the matchfields have been populated.


Of course it really depends on the (largely missing) details of what this is really about. The other thing about Filemaker is that there are usually 4 or 5 ways to achieve the same thing, each with it's own pros and cons.

  • Like 1
Link to post
Share on other sites


Ok so in C#, F#, VBA or SQL I could knock that up in a matter of minutes; but In FM The solution is not readily apparent. I can see a trigger point to execute script but wanted to run by the pro's to see what mechanisms might be most efficient. 



We share the same background... as Comment stated, there are many different ways to skin that cat.


You can certainly handle it with event triggers on those fields that will evaluate both fields and then do an ExecuteSQL() call to do you what you need done.

Event handling has its own issues in that you need to cover all possible scenarios where the user may update either one of those fields.

My preference is to provide for a more guided workflow and UX for the user to update those fields and in that scripted workflow do the necessary fetching of data to update your UI (through an ExecuteSQL() call or through a relationship as Comment alluded to)

Link to post
Share on other sites

I would add that, if you need to find records which match A and B then use global fields and enter your values to perform a find to return the record/s which match those values.  It might look something like (pseudo-script):

If [ IsEmpty ( table::global_A ) or IsEmpty ( table::global_B ) ]
Show Custom Dialog [ OK ; "There must be a value in both A and B" ]
Exit Script
End If
Go to Layout [ based upon a table where these records exist]  <-- if not the current table
Enter Find Mode [ uncheck pause ]
Set Field [ table::A ; global_A ]
Set Field [ table: ; global_B ]
Set Error Capture [ On ]
Perform Find [ ]
If [ not Get ( FoundCount ) ]
Show Custom Dialog [ "no records found matching A and B ]
End If
Link to post
Share on other sites

We share the same background... 


To expand on that statement: I was very frustrated at first by FM's seemingly lack of powerful features; things I could do in C# or VBA in seconds took me many hours to master.  But the beauty of FM does not lay in its developer power.  It is in its deployment power and scalability at a low price

You can design a FM solution that is efficient and will work for 1 to a few hundred users easily enough,  I have not been able to do that in Access and doing the same thing in C# carries a whole different deployment price-point.

  • Like 1
Link to post
Share on other sites

sure you can, but it assumes:

- a very solid understanding of when an event fires, and it's not always obvious

- that you never ever put that same field on a layout without that same event handled.


A scripted workflow without events is preferred, when it is possible, especially when there are multiple fields involved like here.

Link to post
Share on other sites

OK thanks for all the views - some really interesting angles. If I put my exact scenario out there maybe that would help to clarify. 


So, I am working on the concept of a journey. A journey has a "from" and a "to" and a "distance". I have a table of pre-set journeys, so from destination A to destination B is 4 miles say.


SO on my input form, I have two drop downs, FROM and TO. these will contain all the possible "FROM" values and all the possible "TO" values. Based on the users selection from these two boxes I need FM to go back to the DB and look for that combination of FROM and TO values and either return its distance or, a message if it doesn't exist.


I could do that little task quite easily in other techs but I feel things are slightly different in FM. I want to follow best practice.


Many thanks

Link to post
Share on other sites
SO on my input form, I have two drop downs, FROM and TO. these will contain all the possible "FROM" values and all the possible "TO" values.


Bu what exactly is an "input form"? The two drop-down fields, in which table will they be? It sounds like the user will be creating a new record here - a record of what? These are database design questions that need to be answered before you can get to the Filemaker-specific how-to questions.

Link to post
Share on other sites

ok - i have a layout which is looking at my travel table. This holds the name, and vehicle being used. I have a table of journeys that are linked to the travel table. There can be many journeys to one travel so I use a link table that has the FK for travel and FK for journey. The journey table has "from" "to" and "distance". My layout has a portal on it looking at related jouneys records for the travel. That portal has the two drop downs.


So the last piece of the puzzle is the preset Journey table. This holds combinations of journeys with FROM, TO and distance. Ideally, I would have put these pre set journeys on the portal and let my users pick from them, but my customer wanted the ability to pick a FROM and a TO in separate drop down lists and do the maths to get to the distance. The logic based on this would need to be, find the id of the chosen FROM and the ID from chosen TO. Compare those two values against the preset journeys table and return either the distance value OR a message if that combination does not exist.

Link to post
Share on other sites

I am still not fully with you, I am afraid. Basically, you are describing something very similar to an invoicing solution, with Journeys being the products. However, I am confused by this:


I use a link table that has the FK for travel and FK for journey.


If there is a FK for journeys, then you cannot be selecting the journeys by FROM and TO. To select a journey by FROM and TO, your join table must have a FROM and TO fields, too - and the relationship between the two tables must be based on matching:


Join::FROM = Journeys::FROM


Join::TO = Journeys::TO


With this in place, you can simply put the Journeys::DISTANCE field inside the portal (make it non-enterable to prevent accidental modifications) and add a conditionally formatted message text.



Note: This is assuming a Travel will not contain the same Journey more than once. Otherwise, you'd need to get the distance into a field in the join table in order to be able to calculate the correct total distance of a Travel.

Link to post
Share on other sites

Maybe an example may help here.


I have attached a cut down version of the DB. the main view is the transport Ipad layout, here a name is entered, a vehicle chosen and i am going to build a journey.


There is a portal that looks at the JOURNEY table. There are two dropdowns on there with a FROM and TO that take their values from the locations table. When FROM and TO are chosen from those list, I need to take the ID values from the FROM and TO DDL's and see if there is a matching combination in the MILEAGES table. If there is a match, I need to store the MILEAGES.mileage value in JOURNEY.mileage.


As I mentioned in a previous post, there can be many journeys for one claim. If I can get my head round how to do this in FM I reckon I can finish my project. Obviously FM is a different animal to my usual tools, If someone can help me mindset shift I would be most grateful!!


TransportDatabase Copy.zip

Link to post
Share on other sites

You can do an ExecuteSQL() call for each of the records in the portal, against the Mileage table

Do this in a loop for each record in the portal


SELECT mileage FROM mileages WHERE fromID = 6 AND toID = 3


The query will give you all the matching results between those that from and that to, so you'll have to handle that.

Link to post
Share on other sites

Here's a sample script that will "walk" through portal and collect the mileage through SQL.


Note that I'm using some custom functions there to make the SQL syntax safe against field/table name changes.  I'll upload the modified demo file in a bit.

Once I have the matching mileages I arbitrarily take the first one and write it to the Journey mileage field


There are different ways to do this, to avoid the portal walking (I don't don't particularly like the walking) but those would take a bit more work.

Go to Portal Row [Select; First]
Set Variable [$sql; Value:Let(


_distance = _FieldRef_getField ( Mileages::mileage ; True ) ;
_table = _FieldRef_getTO ( Mileages::id ; True ) ; 
_from = _FieldRef_getField ( Mileages::fromId ; True ) ;
_to = _FieldRef_getField ( Mileages::toId ; True ) ;

_sql =
"SELECT " & _distance & 
" FROM " & _table & 
" WHERE " & _from & " = ? AND " & _to & " = ?" 



Set Variable [$result; Value:ExecuteSQL ( $sql ; "" ; "" ; Journey::fromId ; Journey::toId )]
Set Field [Journey::mileage[]; GetValue( $result ; 1 )]
Go to Portal Row [Select; Next; Exit after last]
End Loop
Link to post
Share on other sites

I am afraid I cannot look at your file yet, but perhaps you can be helped by mine:





Note which table the portal looks into.


This is a great solution - No script. I see from this that i need to rejig my relationships slightly and add a calculated field and then the mileage falls in to place for free. Very useful.

Link to post
Share on other sites

This is a great solution - No script.


Comment and I differ a bit on the principle of this.  I tend to prefer a scripted workflow where possible and not stick business logic at the schema level.  Both approaches and pros and cons so feel free to ask about those, keeping an eye on what will give you the best performance / scalability / maintainability / ...

Link to post
Share on other sites

and then the mileage falls in to place for free.


This is what Filemaker is all about. Scripts should be your last resort, not first. I was just about to add that Wim is going to disagree with this, but he already has... :smile:

Link to post
Share on other sites
  • 3 weeks later...

Calculations should be your last resort; unless you are in a GUI only file.


If you need business logic using "calculations" on the schema level I'd say here is how to make sure it does not strangle your performance: http://wethecomputerabusersamongst.blogspot.com/2012/11/filemaker-syncing-data-across-timezones.html

Link to post
Share on other sites

This topic is 2037 days old. Please don't post here. Open a new topic instead.

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 HOnza
      The "swiss army knife" plug-in is now compatible with iOS App SDK 19 and can execute macOS and Windows system scripts asynchronously
      Prague, CZ -- June 23, 2020 -- 24U Software announces the version 3.1 of 24U Toolbox Plug-In for FileMaker, toolbox of the most useful calculation functions for daily use. This update adds compatibility with iOS App SDK 19 and makes it possible to execute macOS and Windows system scripts asynchronously (without waiting for result).

      Version 3.1 brings:
      Function Toolbox_DoSystemScript can be called asynchronously Support for iOSAppSDK 19 added Fixed issue when 3 or more serial numbers are registered which caused FileMaker to freeze Compatibility and performance fixes  
      About 24U Toolbox Plug-In
      24U Toolbox Plug-In is a robust FileMaker Pro plug-in which allows FileMaker Pro databases to:
      Execute FileMaker or System shell scripts to automate tasks inside/outside of FileMaker Pro Execute SQL commands to develop faster and keep your solution clean Match & replace regular expressions to find & replace text fast using patterns Merge data values into text while maintaining styling to quickly and easily apply templates Lookup DNS name for IP & vice versa to find out who is connecting from wher Get current public IP to know more about clients and discover potential security attacks Define global keyboard shortcuts to avoid unnecessarily excessive use of script triggers Obtain precise microsecond timestamps to measure nearly unmeasurable chunks of time Get the type of a FileMaker variable to let your scripts make the right decisions Share variables between databases to avoid creating too many external data sources Get rich text as HTML to generate crystal clean HTML and CSS Support for iOS App SDK and FileMaker Cloud makes the plug-in work on the whole FileMaker Platform  
      System Requirements and Compatibility:
      24U Toolbox Plug-In 3.1 requires any Intel Mac since 2008, 2048 MB RAM, Mac OS X 10.10 Yosemite, FileMaker Pro 15. Recommended is any Intel Mac, 4 GB RAM, Mac OS X 10.10 Yosemite, FileMaker Pro 16 Advanced or newer (including FileMaker 19).
      1 GHz processor, 2048 MB RAM, Windows 8.1 and FileMaker Pro 15 are minimum requirements for using the plug-in on the Windows platform. 1.6 GHz processor, 4 GB RAM, Windows 8.1 or newer, FileMaker Pro 16 Advanced or newer (including FileMaker 19) are recommended.
      For using 24U Toolbox Plug-In 3.1 with iOS are required macOS 10.12, FileMaker Pro or Pro Advanced (for creating FileMaker solutions), Xcode8 (Swift 3.0), iOS 10.0, developer.apple.com account (for testing and deployment on device) and FileMaker iOSAppSDK 18
      Availability and Pricing
      24U Toolbox Plug-In 3.1 is immediately available for download free of charge as a fully functional 14-days trial version, which can be activated after purchasing a license. Licenses for 24U Toolbox Plug-In start at US$49 per user, $499 per server. Volume discounts are available for 5 or more users. iOS app licence for Toolbox Plug-In starts at US $499 for up to 20 users.
      More information: https://www.24usoftware.com/Toolbox/
      Download: https://www.24uSoftware.com/Toolbox#download
      Buy: https://www.24uSoftware.com/Toolbox#buy
      About 24U Software
      With customers in 75 countries and 29 years of experience with the Claris FileMaker platform, 24U excels in developing new or taking care of existing custom apps, optimizing their performance, identifying and resolving potential issues and liabilities, improving reliability, stability and scalability, integrating them with other systems including various hardware devices, and extending them beyond expectation.
      As a Claris Platinum Partner, 24U helps global businesses around the world to maintain sustainable growth by working with their in-house developers or completely taking care of the maintenance and development of their business solutions.
      (c)2020 24U s.r.o. All rights reserved. 24U and 24U Software are trademarks of 24U s.r.o. Claris and FileMaker area trademarks of Claris International, Inc., registered in the U.S. and other countries. All other trademarks are the property of their respective owners. Pricing and availability are subject to change without notice.
    • By dmcs
      Hi. I had a really weird problem last night that was pervasive through all files I was working on, served, local, and even a new file I created to try to test the issue. It was totally crippling and driving me crazy. I had gone into a script and selected the Go to Layout command, but when I tried to select a layout, none appeared, even though there were plenty of layouts in the table. I dismissed it as a temporary glitch and tried a Set Field command, needing to set a field to a calculation, and again, found the system locked up in such a way as I had never seen before. It would not let me enter a calculation! I closed and re-opened the file. No dice. I restarted Filemaker. Nope. I rebooted my entire computer. Nope. I tried working on a local file. Still - no layouts could be chosen in ANY file! I tried making a *new* file. Nope! I was desperate, and just about posted asking for help when I thought to disable all plug-ins, just to make sure, and voila - that did it. As soon as I disabled the 360Works FTPeek and 360Works WebAssistant plugins, full functionality in the script workspace was restored! I thought maybe it was just a coincidence. I re-enabled the plug-ins and checked again. It still worked, with the plug-ins re-enabled, so - maybe I was just having a bad day, but then when I went back in this morning, again - with the plug-ins re-enabled, again - I couldn't select any layouts or make any calculations in Script Workspace, so right away I disabled the plug-ins, again, and again, that fixed it, so I'm pretty sure there's something going on with the plug-ins. I also had 360Works Email installed, but had disabled it the day before any of this started to test functionality on the server, so that one was never enabled, and that's probably the first time I've ever disabled that one while still having the other's enabled. Other than that, I haven't changed anything about my system for quite awhile. I'm actually running 16, so maybe this won't be a problem when we upgrade to 17, but for anyone else who hits this roadblock - disable your plug-ins.

    • By john renfrew
      This has long been an issue, I have no idea why it happens and it is making life quite difficult
      Win Server 2012 FMS 16 (current but this happens on other configurations)
      With ScriptMaster installed 
      run a script which resets the server ( SMReset) and then loads jars and functions return to you the result of SMGetRegisteredModules and show it in a dialog box
      run another script on the server which just returns to you the result of SMGetRegisteredModules and show it in a dialog box
      dialog 1 SHOWS the modules you installed
      dialog 2 is EMPTY
      fmsadmin restart fmse
      do the above again, same result
      this means you can not do a test on the server to see if a particular function is loaded before you want to use it.... in fact there is no test you can do to see if the ScriptMaster has fallen over as far as I can tell.
      Any solutions???
    • By JMart
      Hi gurus,
      I am sure I’m missing something simple, but for the life of me I can’t figure this out. I have a database with tickets. What I want is when a new ticket is created I want to see a popover (see attached) then I want the user to type in the top box and as they type I want to filter the list below (a portal) and once they client is shown they would select it and a ticker is created for the client. I sound a script and modified to fit my needs but can’t get it to work. Can someone stretch a helping hand?
      BTW the filtering is working but its not reflecting in the popover its reflecting on the layout.

    • By Hoytopher
      Hi all,
      I'm an FM novice who has a functioning database, and I'm trying to add layers of functionality to it. One feature I'm trying to design is a script to search through a Gmail outbox with certain keywords.
      In URLs, "%" translates to "%25". Filemaker appears to do this automatically - In Gmail syntax, you need to write "in%3Asent" to have the Gmail search bar contain "in:sent" which searches my outbox. Here's my current code for Go to URL:
      This "%" bit translates to "%25". I've tried "\%", I've tried creating a string, x, then having it open x, but no matter what I cannot get "%" to show up as-is, it always translates to "%25". Is there any way around this? I've tried subtracting text, but I just can't seem to get it to work. Any bright ideas? I can't find an answer to this specific question of disabling URL string substitution.
  • Who Viewed the Topic

  • Create New...

Important Information

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