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

[bruceiow]

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

 

 

[comment]

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.

[Wim Decorte]

 

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)

[LaRetta]

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

[Wim Decorte]

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.

[mikedr]

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

[Wim Decorte]

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.

[comment]

A scripted workflow without events is preferred

 

And a non-scripted one is even better.

[bruceiow]

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

[comment]

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.

[bruceiow]

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.

[comment]

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.

[bruceiow]

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

[Wim Decorte]

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.

[bruceiow]

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

[Wim Decorte]

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

[comment]

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

 

Trips.fp7.zip

 

 

Note which table the portal looks into.

[Wim Decorte]

Here's the file with the script I mentioned earlier

TransportDatabase v2.fmp12.zip

[bruceiow]

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

[Wim Decorte]

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.

[bruceiow]

Brilliant thank you - All understood and I think I have a plan now :-)

 

Really appreciate everyones input 

[comment]

Really appreciate everyones input 

 

 

[bruceiow]

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

 

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

[Wim Decorte]

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

[comment]

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

[ggt667]

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