Can I set an external data source via a variable?

[GC GYM]

I have a separation model file that I use to display some information from my client's main data file via web direct to their customers (customer portal if you like) . It's only external data source is the main data file it reads from.

Each of my clients have their own customised main data file, but the separation file is the same for all of them, except for the external data source.

Rather than host 10 separation files, one for each main data file, I would prefer to only host one, for all to use, and simply change the external data source depending on which file is required.

Now that we can again pass scripts and script parameters in a url (in FMS14), I thought I would use a script parameter to set a global field or global variable on opening and change the path to the external data source accordingly.

It seems that external data sources will not accept variables in the filepath?

Am I trying to do the impossible?

Is an external data source not like a global field, specific to each session?

Would that cause any other connected users to suddenly switch to the new data source?

 

I know, read the manual, answered ny own question http://www.filemaker.com/help/12/fmp/html/odbc_ess.20.5.html

 

Any suggestions for a work around?

 

Edited May 22, 2015 by GC GYM
Read the manual :-)

[James Gill]

AFAIK External data sources cannot be coded to variables.

I'm pretty sure at one point in time FileMaker didn't have the ability to create multiple TO's to reference external data sources or some such (I haven't been using FileMaker for THAT long, but I'm sure one of the other members can point out that limitation) but having to use a separate file for each client's own records is a far from ideal solution. Schema changes must be a PITA...

Is there a particular reason that you're separating out each Clients records into their own file?  Filemaker supports user-based authentication and Permissions Sets, so performing authentication for a particular username/password combo and then assigning the authenticated user a permission-set to view only their own records is FileMaker 101...

 

Edit: Actually I did just think of a workaround.  Just pass the name of the Client's database in the name of the link used to connect to the server.  Each Client would have their own unique link that they need to use, but at least it would get the job done.

Security would be a nightmare though...

/shudder

Edited May 28, 2015 by James Gill

[GC GYM]

Thanks for the reply James. I'm not explaining this very well.

 

"Is there a particular reason that you're separating out each Clients records into their own file?"

By "Client", I mean a distinct company with their own own (similar) database. I don't need a separate separation file for each of their customers, just for each company database.

 

"Just pass the name of the Client's database in the name of the link used to connect to the server.  Each Client would have their own unique link that they need to use, but at least it would get the job done."

I can get the name of the required data source to the separation file via the url. I can set a field or a variable with it in the separation file. I just can't get the separation file to change the external file reference without manually opening define external data sources and editing the filepath.

 

[James Gill]

I just can't get the separation file to change the external file reference without manually opening define external data sources and editing the filepath.

​And you won't be able to because dynamically specifying the external file to use is not supported.  There's a reason it's hard coded.  There are plenty of things taking place behind the scenes in FileMaker server that you never see that are dependent upon that link.

Thanks for the reply James. I'm not explaining this very well.

"Is there a particular reason that you're separating out each Clients records into their own file?"

By "Client", I mean a distinct company with their own own (similar) database. I don't need a separate separation file for each of their customers, just for each company database.

​

You explained yourself perfectly well.  When you say, "I don't need a separate separation file for each of their customers, just for each company database", you mean that you have a separate data file for each client that you work with.  This might make sense if you're using a local version of your data file onsite at each of your clients, but since you're wanting to host this on a server, it makes no sense.  Question: What happens when you want to add a field for your clients?  Answer: You have to add it to 'x' (1?  5?  10? 100?) separate data files.  Hardly efficient, or dare I say it, sane.  

Creating a separate file that contains each Client's own records goes against every database normalization process that I'm familiar with.  Like I said before, I think there was a point in time where you HAD to do that in Filemaker, but that is no longer the case and  this is the root of your problem.  If you normalize your system to create a table that contains ALL records for your clients and a table that Contains all of your clients, you then create a relationship between the Client and the Client's records.  At the most basic level, this prevents one client from seeing another client's records.  This is the fundamental theory between a relational database like Filemaker Pro (or SQL, or Oracale, etc).  Right now you're using your Client specific Filemaker files as a substitute for a dedicated table.

In your first post you said that you're using a Separation Model in your solution.  If it's the Separation Model that I'm familiar with, you have a Filemaker file that contains your UI, and you have a FileMaker file that contains your ALL of your data.  I have no idea how a solution that has a separate database file for each client would be maintainable in the long run.

Using your current model, the only way that you're going to be able to allow each of your clients to connect to their specific data file is to explicitly define ALL of your client's files in the 'External Data Sources' dialog of the Interface file.  Of course at that point you'd then need a dedicated layout for each one of your clients, sooooo......you see the issue, right?

Edited June 5, 2015 by James Gill

[GC GYM]

Sorry I didn't see the reply earlier, but thanks again for replying James.

Yes it seems the only way I can accomplish this is to add every client's own Data Source, Relationship graph and Layouts to the separation file.

And yes, it did make sense at first for a locally hosted file. This was developed over many years for my own organisation using our own in house FMS. It is now quite a large file with 60 tables, some of those with 250 fields. The RG requires a lot of scrolling. A few friends in other companies in our (non-profit) industry saw it and wanted it so I sold them a copy and personalised it for them.

Then others saw it and wanted it too, so I made copies for them and made a few mods to suit them too. Now more people want it and I'm finding that as a potentially commercial product it should have been designed differently.

Maybe I should put up my own FMS and have as many files as I like?

Thanks again for your advice, it has helped me a lot.