Not importing table when it does *not* exist

[ron G]

I am using FM 12 advanced.

 

I have a membership application that works well.

My users have asked for the creation of an Address Book that will track 'unrelated' addresses of non-members.  It works too.

 

The first version WITH the Address Book is v 1.22.  All prior version DO NOT have the Address Book feature.

 

The Problem:  When I Import v 1.21 into v 1.22, my import script seems to import records from Members (presumably since it can not find a v 1.21 AddressBook table). 

 

 

 

I have tried this:

 

... hoping that I could determine if the table existed in the Source file.  But, it does not work.  All I want to do is skip the AddressBook import unless there is an AddressBook table in the Source.

 

So, when you add a new table, how do you get the import script to NOT import to a target table on the first import with a new target table?  (is this clear?)

 

Thanks for your ideas.

 

Ron

[RodM]

Have you tried to put a Debugger breakpoint at that example code, just before, using Tools/DataViewer/Watch mode?  See if you getting into that IF?

[ron G]

Thanks for the reply.

Yes, I set a 'break point' (as per the red arrow above). 

 

And, the if(not isempy(... statement never gets executed.   (I suspect the statement is not checking the Source file for the presence of the layout)??

 

What I need is a way to look at the SOURCE and see if it has a layout named AddressBkImport.  If it does, import from it.  If not, skip the import.

[ron G]

Ok.  I created a 'work around'

There are 2 Import Previous Version menu commands.

Import Prior Verison imports everything EXCEPT the Address Book.

Import Address Book imports Just the Address Book.

 

I put the burden on the user to know if there is a prior version that has an Address Book.  This is kludgey and awkward.  And, it is surprising that FM does not have the ability to look at Source tables and see if a table exists.  Maybe in v 13? 

[eos]

And, it is surprising that FM does not have the ability to look at Source tables and see if a table exists

 

Oh, but it has.

 

http://www.databuzz.com.au/using-executesql-to-query-the-virtual-schemasystem-tables/

[ron G]

EXCELLENT!

I have been postponing tackling the ExecuteSQL function so it looks like I will finally *have* to dig in.

 

When I said "FM does not have the ability to look at Source tables..."  I mean 'natively' without using ExecuteSQL.  But, it looks like it will be a solution I need to get familiar with. Thanks for the 'nudge'.

 

Ron

[ron G]

I've spent the last several hours with ExecuteSQL and don't see a way to use it to 'look' into the Source file and determine if the source .fmpur file has a specified table. 

 

The paradime I imagine is something like: 

* User browses to the imported file and the path is collected as $$path and $$FileName

*ExecuteSQL 'looks' into the $$Filename and determines if there is a table called AddressBook

*If there IS such a table, importing takes place.

*If there is not such a table, importing into Target::AddressBook is skipped.

 

I can't get started in understanding what the ExecuteSQL command would look like.  The examples I've seen all deal with an open FM File... not one that is an import source.

 

Thanks

Ron

[eos]

The paradime I imagine is something like: 

 

I think it's more a strategy (or maybe an algorithm) than a paradigm, but I know what you mean … 

 

Anyways, what you need to feed to ExecuteSQL is a TO name, which in turn is based on a native table or an External Data Source. So if you define an EDS, you can use that in the query, without even having to open the other file.

 

So in this example, ExecuteSQL ( "SELECT * FROM FileMaker_Fields WHERE TableName='AddressBook_external'" ; "" ; ""  ) gives you a list of fields from the file AddressBook, if there is a TO named AddressBook_external, and the external file AddressBook it's referring to is based at the specified path (or one of the specified paths), as per the External Data Sources dialog box. Otherwise, your favourite SQL character will appear.

[ron G]

I think my problem is that I can't put a file in the EDS because at the time of import the file is unknown.  Some may, for example, import v 1.11 into 1.22 .  Others may import v 1.17 into 1.22.  I have no knowledge of what the EDS will be so I can not provide for it.  

 

Or, am I mistaken?

 

Thanks

 

Ron

[eos]

I think my problem is that I can't put a file in the EDS because at the time of import the file is unknown.

 

If the file is unknown, how do you import from it?

[ron G]

When I say it is unknown, I mean it is unknown to the program 'until' the user selects the import file.  That file name is uknown until it is selected.  So, how can I add that 'unknown' file to EDS after it is selected?

 

R

[eos]

There are plugins that allow capturing the user selection in a file dialog (MBS and BaseElements FM, I think).

 

That being said, I was assuming that the user just needed to place the new file in a specific location (say, the same folder as the old file), then click a button to have the import run its course.

[ron G]

There are plugins that allow capturing the user selection in a file dialog (MBS and BaseElements FM, I think).

 

That being said, I was assuming that the user just needed to place the new file in a specific location (say, the same folder as the old file), then click a button to have the import run its course.

Yes, I am using ScriptMaster and capture the $$Path and $$FileName. 

I don't know how to 'embed' that variable name into the EDS?

[eos]

Then I guess there's the rub, because ESD doesn't accept dynamic file paths (and I didn't think this through completely).

 

The only thing I can think of at the moment is to import a small bit of data (like one record from a utility table; maybe even the version number itself?) that tells you what kind the import file is, and based on that, import the address data or not.

[ron G]

Sorry, I guess I wasn't clear.

The problem is determining if a specific table is present in the Source file using the Import Script in the Target file.

 

I know, this is a tough one.  Too bad FM doesn't allow us to natively look at the tables in the source prior to import.

 

Surely I am not the first developer who needed to add a table to an application after it was distributed?  Surely, there is a 'work around' for this problem?