Jump to content
Sign in to follow this  
bruceiow

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

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

 

 

Share this post


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

Share this post


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)

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites

Could you use a script trigger once a user has entered/exited/modified a field to perforom the logic?

Share this post


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.

Share this post


Link to post
Share on other sites

A scripted workflow without events is preferred

 

And a non-scripted one is even better.

Share this post


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

Share this post


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.

Share this post


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.

Share this post


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

AND

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.

Share this post


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

Share this post


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.

Share this post


Link to post
Share on other sites

thank you - how do I fire that executeSQL event and how do I reference to the specific field once im in the SQL? i.e. i need to say "WHERE fromID = currentportalrow,from .

 

Many thanks

Share this post


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]
Loop
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 & " = ?" 


];




_sql




)]
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

Share this post


Link to post
Share on other sites

Hi and thanks - I am just going to analyse the DB. Can I just ask, where is that script being called from? many thanks

Share this post


Link to post
Share on other sites

I just left it in the scriptMaker menu, so you can run it from there.  You can slot it into a few different places depending on your workflow.

Share this post


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:

 

attachicon.gifTrips.fp7.zip

 

 

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.

Share this post


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 / ...

Share this post


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:

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • 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.
    • By Agentshevy
      Hello everyone..
      Guess I have been away for a while. Not fully back though because I am not really engaged with computer tech stuff at the moment. However, I really stumbled across a problem earlier today while going through my email logs. I observed that a server script I had set up has not been attaching the Pdf file report as supposed to. It only delivers the message while leaving out the file attachment. I did test the script when I added it but what I did not test was the result when run by the server.
       
      Allow user Abort [Off]
      Commit Records/Requests [No dialog]
      Set Variable [$FileName;Value:"Summary"&".pdf"]
      Set Variable [$PDFPath;Value:Get(TemporaryPath) &".pdf"]
      Go to Layout ["Summary_Layout]
      Perform Script ["Print_Page_Setup"]
      Save Records as PDF [Restore;No dialog;"$PDFPath";Current record]
      Send Mail [Send via SMTP Server;No dialog]
       
      As at the time of my writing the script, I was not observant about server-side scripts. I did go back just recently to observe that the "Save Records as PDF" script step is not server compatible.
      Please, is there any work around on this? Much help is appreciated.
  • Who Viewed the Topic

    10 members have viewed this topic:
    tbcomputerguy  sfpx  NateJames  Ocean West  fishtech  NLR  BruceR  Lee Smith  Jesse Barnum  doughemi 
×
×
  • Create New...

Important Information

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