Solved

Should I import or Link

Posted on 2016-08-31
25
43 Views
Last Modified: 2016-09-21
So we have FileMaker Pro here and it holds our equipment inventory, however there are a few fields that I have been wanting to add to it but the DB Administrator won't add them.  Keep getting the I can't add anything once it is created,  I am sure they could, but either don't want to or they need to run it through a CCB.  

So I can export the equipment inventory to excel, which is great.

But I want to link or import it into access and add the fields we need there.

So here is what I am asking:
Would it be easier to link the spreadsheet to the database and if so how would I add my extra fields?
If I have to import it I can add the field there but how would I update the data from FileMaker pro each time I updated the imported spreadsheet without affecting the fields I have added?

Suggestions?

John
0
Comment
Question by:John Sheehy
  • 11
  • 7
  • 3
  • +2
25 Comments
 
LVL 17

Assisted Solution

by:xtermie
xtermie earned 125 total points (awarded by participants)
ID: 41778203
Linking sounds better to me, but you should create an Excel sheet that will act as the "intermediate" table between the exported data and your additional data.  You will be able to create this after the first time you export the data from your database and insert the extra columns.

After that, using ODBC link the Excel table to the database, create the LOOKUPS necessary to retrieve the extra data and don't forget to update your intermediate table (sheet for lookups) everytime new data is needed.
0
 
LVL 11

Accepted Solution

by:
CraigYellick earned 125 total points (awarded by participants)
ID: 41778204
How many records and how many fields are in the exported spreadsheet? How often are records added and deleted in the source database?

I could see using either a linked or imported table depending on circumstances. Linked is more convenient process-wise but can be very slow for large numbers of records. Importing gives you more control and faster performance.
1
 
LVL 17

Expert Comment

by:xtermie
ID: 41778209
Craig is right that you should consider the number of records.
0
 

Author Comment

by:John Sheehy
ID: 41778214
So I should export the data from File Maker Pro.  Copy and rename the file and add my extra fields then link that table to the access database?

The issue I see with Linking is the data can not be updated.  The extra fields would need to be updated at least once a quarter.

I have written code before where once the file was imported into it's own table the information was then copied to the new table.  Which worked really well.  The issue there came in is when the table being imported didn't have the same fields as the the table it was imported into.

So for example:
Let say I import 5 records and then copied those records to the working database and updated the fields that were added,  Well the next quarter comes and I do the same process again only this time one record was deleted from File Maker Pro and one was added.  The import doesn't care and doesn't distinguish between added/deleted.

Suggestions?
0
 

Author Comment

by:John Sheehy
ID: 41778218
We are talking 3000 records and 15 fields
0
 

Author Comment

by:John Sheehy
ID: 41778223
I guess the hard thing is I am wanting to add extra data that is not in the File Maker Pro database. which would make updating data in the standalone database harder.  Unless no data is ever deleted from the FileMaker Pro version.  Something to ask though.
0
 
LVL 11

Assisted Solution

by:CraigYellick
CraigYellick earned 125 total points (awarded by participants)
ID: 41778224
Regardless of import versus link, the Access drivers for Excel can make some bad decisions about data types and generally make things miserable. Before going much farther, do some trial imports and make sure the data comes over cleanly. Look for null values in records where you know there's data in the source system. Excel is my last choice for data xfer. If your DBA can generate a tab-delimited file that's the best way to go.

Dang, I've got a conference call starting up... will check on this thread later.
0
 

Author Comment

by:John Sheehy
ID: 41778231
I can do tab delimited with no issue.  I had no issue importing the data a few times straight from excel.  Either or works.  But I will go with Tab for now to lessen the chances of an issue happening.
0
 
LVL 11

Assisted Solution

by:CraigYellick
CraigYellick earned 125 total points (awarded by participants)
ID: 41778243
3,000 rows x 15 fields should not pose a performance problem for a linked table.

My first reaction is to create a parallel table in Access that carries the primary key of the equipment record, along with the additional fields you want to track.  When you get an updated file you'd first check for new keys and insert records to your parallel table. Deleted records can be detected and processed as well.

This should work whether you link to the equipment file as-is or import it. Linking is easier.
0
 

Author Comment

by:John Sheehy
ID: 41778249
Linking doesn't allow you to update the records from within access, does it?
0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 41778252
Updates over links are possible, it depends on the file type. Do you need to be able to update the fields that are stored in the equipment app?
0
 

Author Comment

by:John Sheehy
ID: 41778259
I do not, those changes will actually be done in File Maker Pro.
The only changes I need to make will be to the fields that I need to add after the first import.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 11

Assisted Solution

by:CraigYellick
CraigYellick earned 125 total points (awarded by participants)
ID: 41778285
Read-only, that's perfect. A linked table should do the job with less hassle.
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 125 total points (awarded by participants)
ID: 41778296
Linking directly to the FileMaker tables from Access would be a better solution.  That way changes to your data in Access are reflected automatically in FileMaker.  You would do this via and ODBC driver.  Problem is, you need to have ODBC Sharing enabled in FileMaker but it sounds like your db admin isn't very willing to help.  Here's a link from FileMaker support that shows how to enable this.  It basically says to open the ODBC/JDBC Sharing Settings dialog box and enable ODBC/JDBC.
Edit menu > Sharing > Enable ODBC/JDBC.
They would then open up the ODBC Data Sources program and add FileMaker along with its security credentials.  Then you can link directly to any of their tables from Access.  However, without your db admin's help, this is all academic.

Ron
0
 

Author Comment

by:John Sheehy
ID: 41778300
Great idea about linking straight to File Maker.

Linking is great but I still need to be able to add my 5 additional fields and how would they be updated.  Query maybe?
0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 41778304
Once linked to an external table source, the process is identical regardless of the driver. Linking directly FileMaker Pro would be slick if it is supported. I have no experience with it.
0
 

Author Comment

by:John Sheehy
ID: 41778307
I will work all suggested solutions today and let you all know this afternoon (CST) of the outcome.

Thanks
John
0
 
LVL 2

Assisted Solution

by:psteff
psteff earned 125 total points (awarded by participants)
ID: 41779830
The thing I did not notice in this thread is – are the 5 extra fields totally specific to each individual record or are there groupings (such as all equipment ID’s starting with “c” get certain things entered into the extra fields, ID’s starting with “f” get another set).

Depending on what the data really looks like and how the extra five fields relate to the original data, I would think either Excel or Access would pretty much equally do the job – but I don’t think I would use both.  I would just try to use either Access for all of it or Excel for all of it.

An option that I did not see mentioned is just to link (or import) the FileMaker Pro (FMP) table into Excel in a table structure.  In addition, you would have a table(s) in another worksheet in the same workbook with the information needed to fill in the other five fields.

If the five fields are totally specific to each individual record, you would have one table with the ID number (or some unique number specific to the record) and the information for the other five fields.  You would then do a vlookup to this table from within the imported FMP table to populate the extra five fields.  You could do conditional formatting in the FMP table if you want to quickly see which records are not in your extra table and then go to add the information into your extra table.  With only 3000 records and 15 fields, I don’t think you would have a performance issue.

If the information for the extra five fields are such as in the second case above in my first paragraph (some type of grouping based on some type of data in the original file), then you would need multiple extra tables with the appropriate index/reference field for the FMP table to do the vlookup on.
0
 

Author Comment

by:John Sheehy
ID: 41779876
So here is where I am at:

I can not link to the FileMaker Pro DB.  The SA says no way.
I can export the records to an excel workbook/sheet and I can import all that data into access with no issues

The five extra fields have no info yet.  But they will

The issue we are running into is when I need to trace a cable I have to find the drafter and have them pull the drawings so I know where the cable is supposed to go. Which is a lot of time a pain because they are so busy and they have very little time to pull drawings.

We suggested that if we added the ports from the switch / router we could enter the port number and the cable ID attached to that port.  Then we could run queries to find where the cable runs to.  That is two of the fields the other three fields are POC, POC Phone and POC Location.  Because believe it or not the POC may not even be in the same state/country.

My only concern came in when updating the data from the FileMaker Pro side which does not have the five fields.  If a switch was deleted in File Maker Pro, Access would not know that.  It may just have to be a manual compare in a temp table and then copy into the main table.

John
0
 
LVL 2

Assisted Solution

by:psteff
psteff earned 125 total points (awarded by participants)
ID: 41779893
From your table(s) for the extra five fields, you could do a vlookup with the “false” option to look for an exact match in the FMP table.  If it returns false, you know that the switch has been deleted.  This would be much quicker way that other types of manual comparing and simpler than setting up a temporary table.
0
 

Author Comment

by:John Sheehy
ID: 41779966
What is this vlookup you speak of?  I have never heard of it before.
0
 
LVL 2

Assisted Solution

by:psteff
psteff earned 125 total points (awarded by participants)
ID: 41780034
I don’t know if you have decided between Excel or Access.  The vlookup is in Excel.  If you are deciding on Access, the following is not relevant.

Syntax of the vlookup is
=vlookup(lookup_value, table_array, column_index_number, [range_lookup])

So, if your data imported from FMP starts in row 2, and you want the first extra field data in column P.  In cell P2 of your FMP table, you would enter a formula similar to:

=vlookup(a2, Sheet2!$A$2:$F$3000,2,false)

Where:

A2 (or B2 or whatever column) is your ID number or the value in the FMP table that will reference your extra table.
Sheet2!$A$2:$F$3000 is the address of your extra table – change to wherever the extra table is located
2 is the column of your extra table that your information is in (change as necessary)
False is to make sure you want to return the value of an exact match only

The above formula should be modified to reference wherever your specific data is located.

Once you have the first formula in P2 (or whatever cell), then copy the formula down and you will get the extra field.  Do a similar formula for the other 4 fields/columns.
0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 41780039
Access VBA has an equivalent function, called DLookup. Though in this case DCount would work as well. Can also use a SQL statement. All can detect the non-presence of a key.
0
 

Author Comment

by:John Sheehy
ID: 41780043
Both are awesome solutions.  I will try them out right now.
0
 
LVL 17

Expert Comment

by:xtermie
ID: 41808381
Good comments n recommendations provided
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now