is FM the right choice for me?

[titanium]

first off, i apologize if this isn't in the right place, but not sure ware it really should be.

I work with an inspection company. We want to centralize our clients and inspections into a computer bassed application.

We have a few inspectors but cant always be at an internet connection. Each inspector would have the DB on there laptop and be able to lookup clients and then choose the inspection they are to do and fill in the forms appropriately. However, we all want to have the data updated so that we all have the same information.(inspections, clients and all) Does file maker really allow us to "Sync" data across multiple instansis of the same DB? If so, any recomendations how i should go about starting. i have built a rough DB as a starting place, but dont want o go any further if FM isnt really the best choice. We dont have an office we go to each day so want to be able to sync via the internet somehow... Are we able to create a import/export script that would keep all DB's sync'd?

Thanks for any thoughts on this.

[Simon K]

I am pretty sure there are no synching abilities in native filemaker ...

Others may correct me but I think FM is the wrong choice for the sort of application you are looking to build

Maybe something on sybase SQL Anywhere (which is very good at synching and relatively cheap)... though you would have to decide your development platform

try this link http://www.sybase.co.uk/products/databasemanagement/sqlanywhere

hth

Simon

[LaRetta]

Titanium, you might also try SyncDek .

[Joe King]

titanium:

This sounds like a perfect application for FileMaker... The 'collection utility' could be done with FileMaker runtime- meaning that you have all the 'ease of use' of FileMaker, keyboard shortcuts, and robusost interface. Runtime solutions can be delivered to multiple users without a per user expense.

There are a number of providers (inclusing myself) which offer FileMaker hosting, so when the inspectors are back at the office the 'utility could' post to the 'network solution'.

If I understand your situtation you need a mobile data collection tool, more than you need complete database functoinality on the road.

For the record, most 'professional' FileMaker developers can sync a FileMaker Pro database, but I'mnot sure that two-way syncs are required here, and one-way is much less complex.

Another option is to use cell-phone based mobile broadband cards, as I type this message, I'm sitting in a resturant using a Sprint card.

Third thought, Sybase is not cheap, plus it has to be 'installed' on each machine. I recently completed a 'Tennis Match Scoring App' which ran from a USB Flash drive- no installers, no admin rights- just put it in and it collected data. In that solution the USB was returned to the 'main machine' and the collected data was pulled from the runtime solution.

IF you really need full two-way syncand you don't wish to have a developer create a custom solution for you then you may wish to look at the WorldSync products...

In short, FileMaker has plenty of options, and should be conisdered for your project. Another factor is that FileMaker solutions are less expensive to development than most of the other options on the market as well...

Thanks,

[titanium]

Joe,

Sounds like not all hope is lost.

to clarify my needs further....

Each inspectors computer (and the main office computer) all have to stay in sync. If i do an inspection and while on site some of the customer data is updated, then the other inspectors need to know in case they get the call to go back at a later date. Also, each inspector needs full access to all data in order to review any issues upon call back to a site. (emergency calls etc).. So most of the database functionality is required on the road as well as in the office.

So, two way syncing is needed. We are a small company but have a large client base. We dont however have really deep pockets so much of the development of the core app im wanting to do myself but am certainly open to the idea of a pro coming in to tie up all the loose ends so to speak.

Whatever the solution will end up being, i am adament that all data is under our full control and no other party has access, or potential access to the information.

Your insight is valued.

Thanks

Edited January 10, 2009 by Guest
more clarification

[Joe King]

Okay...

For the record, I still like Broadband card because it keeps life simple, but we'll run with the idea of syncing this thing.

I did a database for a large Christian ministry and their where lots of users where adding data, but syncing was fairly simple because 95% of the users where adding data to the database.

In reality, you will likely have 30-40 tables in your database solution, some will need one-way pushes to the server, others will be one-way pulls from the server, and hopefully we can keep the number of 'true-way' syncs to a reasonable level.

I foresee trying to keep a history of all activity, so as long as one inspector is not changing existing data, then syncing is not too difficult.

If you have a database already started,I could give it a look and see what I think...

Thanks,

Joe

[Genx]

where=were. I normally don't care but that was really hard to read.

[comment]

I think it needs to be "there were".

I always care.

[Simon K]

You know I don't really mind being corrected - but what I said was that "there are no synching abilities in native filemaker" and I don't think anybody here has said anything to contradict this.

I also am not particularly keen on inferred slurs - "most 'professional' FileMaker developers can sync..." etc - I just think its so unnecessary to be judgemental and probably unwise.

For the record - we use Sybase to deliver end to end retail systems to hundreds of multi site businesses - funnily enough all using the native replication/synching in the Sybase toolkit.

To give you a little information that you are clearly unaware of - Sybase does not have to be on each machine and costs from as little as £65 per user (oem pricing - but I believe a full development copy only costs £295) - so yes - that is what I would call cheap - granted its not free!

We use Filemaker for some of our internal systems - precisely because of all the normal reasons - ease of use, (now) great integration with SQL Server, flexibility and robustness. And, not least, because our professional developers can get on with developing our IPR in a variety of tools, whereas I can deal with the Filemaker requirements or farm them out at a much lower (opportunity) development cost. (If you are not sure what I mean by opportunity cost - in this instance I simply mean that we can make more money from our professional developers working on fee paying projects than working on internal admin systems dev - no connotation of lesser value).

Like many people I like the relative ease of programming in FM but if I were building a third party app or a mission critical app I am not sure I would want to rely on a "plug in" or similar for something as fundamental as data movement.

I am aware that different people have different views and if you can gain the comfort in the reliability and long term viability of the provider then maybe that is the way forward.

I just believe in using the right tool for the right job but that is up to Titanium to ultimately decide.

At Titanium - I think you should be aware that programming solutions for multi site operations requires quite a lot of planning and some of the issues you come across are not obvious from the outset.

As an example you need to have a method for handling the situation when comms fail, when synching has failed etc, that maintains the integrity of the data when it is eventually synched re/synched.

I would also be wary of import/export solutions - these are not the same as true replication in a single database solution and may lead to further complications, eg as Joe King says, when remote users need to edit exisiting data.

True replication should be able to be carried out as a background process in real time (I realise that you have said that your users may not always be on-line) and uses the database's internal features to create check points etc to ensure all data is processed, whereas import/export solutions will need flagging, maintenance checking etc to be coded to recreate these checks.

As a final thought - I am not recommending Sybase per se (it happens to be what I know about and has a particular emphasis on mobile data) - I am simply advocating getting the right tool for the right job and if the tools mentioned by Laretta and Joe King do the job and achieve all of the objectives then that's all good and gives you choice.

Edited January 11, 2009 by Guest
clarification of internal dev cost vs external chargeability

[comment]

I don't think you were being corrected. We don't have enough information, but from the little description given it seems like true synchronization wouldn't be required here - I think that's what Joe was saying.

I also didn't perceive an inferred slur upon you. I could be wrong about that (the quotes around "professional" do seem rather odd in the context), but I believe that given the limitation of the medium one should always choose a favorable interpretation, where possible.

That aside, I think I WILL contradict your statement: Filemaker does have at least a primitive synchronizing feature, through its ability to import while updating matching records. Using a simple relationship of:

Target::ID = Source::ID

AND

Target::ModificationDate < Source::ModificationDate

you could isolate source records that are more current than their target counterparts, and import only those. Repeating the same process in reverse should result in the two tables being equal. It's not something I would look forward to implementing, but it's there.

[LaRetta]

My mistake here was assuming that the person posting (who rated themselves as Beginner) as asking if THEY could succeed at such a task instead of reading it properly as - could FILEMAKER handle the job in general. We get so used to the person who will accomplish the task being the one posting. I took Joe's use of "professional" to mean just that (an advanced developer instead of a beginner) and I don't feel slur was intended at all.

you could isolate source records that are more current than their target counterparts, and import only those. Repeating the same process in reverse should result in the two tables being equal.

Absolutely true that FileMaker can synch (to a degree) but I clearly read in opening post that two-way was needed. And synching can be nightmare ... picture two separate databases with exact copies of the Client table and the external rep changes the client's last name and an internal employee finds out the Client's birthday and adds it (within the same record), then what?

Letting external reps connect via broadband is perfect (and easy) answer because no internet connection is required. My somewhat limited experience with broadband (which is why I didn't consider this option) indicates they may at times be frustrated with connection issues or speed but then ... that's computers for ya.

It'd be cool to program it through their blackberries - I know it can be done!

PS - Michael, I realize you were just making a point that synching is possible with FileMaker so I wasn't disagreeing; only mentioning that I saw the need for up-to-date information available to all employees (whether internal or external).

Edited January 11, 2009 by Guest
Added ps

[Simon K]

To Michael and Laretta,

I am aware of the ability within FM to do an "updating" import. In fairness though, I do not think (my opinion) this gives "synching tools" and it would be quite a task to implement a robust solution especially if it needed to be bi-directional.

I suspect that I was being over sensitive - not for the first time, and I thank you both for pointing that out - I shall interpret the comments in a more favourable light.

Cheers

Simon

[LaRetta]

I do not think (my opinion) this gives "synching tools" and it would be quite a task to implement a robust solution especially if it needed to be bi-directional.

We are all in agreement, I think.

LaRetta

[comment]

the external rep changes the client's last name and an internal employee finds out the Client's birthday and adds it (within the same record), then what?

Then the primitive method I have outlined will fail. If such possibility is anticipated, you'd need a 'modified' field for every relevant field, and the entire process would have to be repeated for each such field. Of course, it would be quite a nightmare to do this entirely within Filemaker - but not impossible.

However, when I read the original description I get the impression that it's just a matter of updating the central database with something like "I inspected client #345 today; their name has changed to 'Acme, Inc.'; details of today's inspection follow". Assuming no one else deals with the same client at the same time, this could be done even by e-mail.

[titanium]

Sounds like ive opened a can of worms here. I have enjoyed all the insight offered by everyone who has posted. I've realized that i may be looking into a solution far to complex for our relatively small company. I will clarify further as i really want to be sure that FM is going to be right before we start dumping resources into it.

We are a fire protection company and do annual inspections on suppression systems, hydrants, backflow preventors and extinguishers.

Our client list is a few hundred. Each client has multiple items requiring inspection. (ie.. two supression systems, one backflow, and ten hydants)

We also handle all emergency calls to our clients in case something breaks. Not always is the same technician going to the same client site, thus the importance of having information shared.

Upon doing an inspection i have to fill in a report and submit it to the office for billing who then sends it off to the proper channels such as the fire department, city and client. The data collected is required to be accessible the next time any of the techs go to the site for any reason as they may need to know the location of a certain valve or room. We also need to be able to review the previous years inspection reports to monitor for any major changes in function of the system or component.

So, as far as i can see the data has to be able to be sync'd with all the computers (at this time 3 or 4). Syncing would be done at least weekly but in most cases daily. We do not have the option of a internet based solution as many of the places we work in don't even get cell reception (parkades, mills, etc) which is why we have to consider an off line solution.

Any of the data may be edited at anytime ie. client contact person may change and i would update their file when im on site.

I hope this helps to clear up what im trying to create and helps to establish a common agreement of weather or not FM is the right choice for me.

Thanks so very much for all the time you are offering to provide advice.

I have attached a copy of the VERY rough Db ive started. This has not pretty formating, and much of the function is not in place, but like i said, i want to be sure FM is right before i go any further.

cust_management.zip

Edited January 11, 2009 by Guest
added file

[comment]

The crucial point here is this: can the same record, (say a client record) be edited by more than person at the same time? By "the same time" I mean in between updates (what you call 'syncing'). If, for example, it's possible for a client to call your office and have some details updated while one of your inspectors is visiting there, that could be a serious problem. Otherwise I don't see why an inspector couldn't download up-to date data in the morning, go to work and upload a report in the evening.

[titanium]

Its unlikely that any file would be edited in the office while the inspector is on site of the client. So, no single record would be edited at the same time. But... what would happen if that were to occur under some extra un-ordinary happening.

[comment]

In worst case, one of the edits would be lost. Even that could be prevented by changing the workflow so that inspectors can upload only requests for changes, and having someone go over these before approving them. I suppose inspection details could be passed automatically, but changes in client and equipment would be examined.

[LaRetta]

It is also possible to work out an agreement between staff. I had to nightly sych 240 databases (years ago prior to FileMaker, using Approach). Staff just agreed that changes to certain fields in Client could only be made by office and the remaining fields could be handled by outside techs. If some blanket understandings can be reached then Michael's methods will work; simply don't import (from external staff) those fields which are 'owned' by office and vice versa. You might want to firm up some business rules around editing and see if compromises can be reached; it would save quite a bit of money no matter how you go.

No database completely handles synch properly because of this issue. Act says it synchronizes but in fact it simply imports and field-by-field modification considerations are ignored. I have heard from trusted developers that SyncDek takes these factors into consideration (timestamps every modification of each field) but I have never tested it personally.

[titanium]

this sounds so very overwhelming. Not sure if im ready to take on the task of making this happen. I really have to do more research to be sure im not jumping in over my head : it just seems that the complexity of this process is going to cost a small fortune, so just have to be cautious. Hmmmmm....

[comment]

It's not THAT complex, you just need to switch your paradigm: instead of multiple databases that need to be synchronized, think of a single central database that needs to be updated by multiple sources.

[titanium]

OK... im starting to get my head around this. So... here is what im thinking...

The main database (at the office) needs constant updating. Daily perhaps, from each laptop, by data input by each inspector.

Each inspectors laptop need only be updated weekly or monthly... just to stay on top of things, but not mandatory to be done daily by any means.

Inspection reports are the primary item which will need to be updated to the main database. Customer fields may be updated by the office only, or by a separate"request for customer information update" form which the inspector would fill in and submit just like an inspection report, then the office would manually update the required fields.

Each inspector would email in the daily update file and the office would receive this and import the file into main database. when the inspectors laptop file needs to be fully syncd with the main DB, a copy of that would be transfered onto the laptop at the office via the network.

So... that being my understanding of the structure, whats the bottom line. What is needed to make this happen.

I have already posted the DB ive started. It seems to me to have 75% of the function already. Is it just a matter of finishing it off 100% then adding the syncing feature on top, or is that required to be built in along side of the entire development process? whats it really gonna take to make this work?

[comment]

I would just add that inspectors do not need to "sync" their laptops - they can start with a fresh copy of the central database.

I believe you could start by building the central database under the assumption that it's only going to be updated MANUALLY by the office. You also need to build a separate mechanism for the inspectors to report the updates to the office. Then you can gradually automate the "missing link".

I would probably split the solution into three files: a data file, the office application file, and the inspectors application file. This way you can easily distribute the data to the inspectors, without needing to replace the user-interface portion (and vice-versa).

There is one delicate point here that needs attention: if an inspector adds a new hydrant, for example, and adds it along with a related record of the hydrant's inspection, you must make sure that when the new hydrant is imported into the central DB, its inspection record remains related to it. Otherwise it may get mixed up with another hydrant added by a colleague (since both started with 50 existing hydrants, they both added hydrant #51). This could be solved by prefixing ID's with a unique inspector code, for example.