Additional Information: This appears to work fine if the file is local on the computer, but when hosted on the FileMaker 9 server, it breaks. Any ideas?
when I pull this data via ODBC into a label layout
Could you elobaorate on this?
BarTender will allow connection to the Packaging database via ODBC. I have an ODBC connection setup using the Sequelink 6.0 driver (I've also tried using Sequelink 5.5 supplied by FileMaker). BarTender is configured to prompt for a lot number which is a unique key field in the database and then the fields are displayed on the label layout based on the found set (record).
The label design looks something like this:
Description (aka product)
Cat #: Cat_Num Lot #: Lot
Exp: Exp_Date (date field)
Sell By: Sell_By_Date (date field)
Store at Storage_Temp
bold text are fields from the database via ODBC
And even more information:
I've also tested with another label software program - Teklynx LabelView 8.2 - and it does the same thing. This leads me to believe it's specific to the Sequelink ODBC driver in some way, but why does it work fine locally versus hosted on the server? Good times!
Did you set up the System DSN on the box where FileMaker Server is being hosted?
Actually, No. I don't have a lot of experience with ODBC, but all of my previous usage only required the DSN be setup on the local machine that is referencing the system. The FileMaker Server shows that the database is being hosted with ODBC (checkbox) and I'm able to get data from the file via ODBC, it's just these calculated fields that aren't stored that are problematic.
If for some reason that data is static, it works fine. It also works fine calculated on my local machine via ODBC to the label software.
When setting up the DSN on the local computer, I specify the port and actually select the database from that DSN connection, so I'm not sure how a Server System DSN would help.
I'm willing to try though... how would it need to be configured and what does it accomplish?
You should have the same System DSN on the server itself. A quick way to understand what is going on is open the served file via Open Remote from Pro/Pro Adv.
Go to manage external data sources. You will not see the DSN that you have selected when you had the file locally as a choice... You have to have it on the server. Once on the server, you dont have to have it locally if you dont want to.
OK, so let me make sure I'm clear on this...
If on my local machine, I have a "User DSN" setup as "FMPack" that points to my server IP address and 2399 port for FM Server, then on the FM Server, I also want to setup a "System DSN".
Should it be the exact same name -- "FMPack" and should it be set to the 127.0.0.1 IP? I'll need to install the Sequelink driver on the server, but I can do that during off-hours.
I think that I have been missing you... I am no longer sure what is external vs. FileMaker.
In your first post you mention a "Label Layout"... Is this a FileMAker layout or layout in this BarTender software?
You also mention "Master Products" which is an external reference... Is this a reference to another FM file or external? If FM file, then is it on the same server?
Do you have Server or Server Advanced? Only Server Advanced gives you an ODBC option between the two.
We are running FileMaker Server Advanced 9 and the FileMaker database (Packaging) is hosted there and shared for both FM and ODBC.
The label software is BarTender and is local on my computer and grabs info from Packaging via a UserDSN using Sequelink 6.0.
If I host the database locally on my computer via FileMaker Pro 9 and setup a 2nd UserDSN to point to 127.0.0.1, the date field (which is calculated) works fine.
If I pull the exact same date fields from the database hosted at the server level, the date is missing the calculated information and is only the base value. This field is not stored as it relies on values from a related file (Master Product) also hosted on the FileMaker Server Advanced 9 setup.
Hmmm strange. Try importing the data into Excel using the same table and unstored calc via the ODBC driver.
Whenever we import data via Sequel Link 5.5, through FMS, it seems as though the data in calculated PRIOR to the grabbing of the data by the ODBC driver.
IOW, if I import into Excel or mySQL, it will give me the correct calculated result...
I do find it strange though as if I were to deduce an outcome, it would not calculate as there is no client calling it to perform the evaluation... Perhaps the ODBC driver compensates for it, although it seems in your case it does not.
Please perform the import into Excel test to see if you get the same results.
If I use Excel and pull data via my UserDSN (Sequelink 6) to FileMaker Server 9 Advanced, it gives me the incorrect data (the date without the additional calculated value), but if I use the same UserDSN on the file hosted locally (127.0.0.1), it works fine.
This is frustrating! It makes it appear like it's a problem with FileMaker Server 9 Advanced or something...
I dont know what to tell you. I replicated your scenario. I used an ODBC connection to FileMaker Server Advanced 126.96.36.1995 n a Windows Server 2003 box. I had two tables and even named the tables the same as yours. The ODBC imports the calc fine into Excel.
The only difference is that I am using a System DSN on my Desktop with Data Direct 32 bit Sequel Link 5.5 driver.