14 Replies Latest reply on Jun 16, 2009 8:57 AM by mrvodka

ODBC Calculated Date Problem

csandoval Jun 10, 2009 3:10 PM

Title

Post

I'm using BarTender label software to create labels from data in our Packaging database. I thought I had everything working OK, but noticed a date discrepancy on 1 of my automated labels that has to do with an expiration date. This expiration date is a calculated date based on a related file called "Master Product" that contains a detail list of all products including a field called "month_life" which is the shelf life of the product in months. The calculation works fine in my Packaging database and it looks something like this:

Exp_Date = Date (Month(Bottling_Date)+Master Product::month_life;Day(Bottling_Date);Year(Bottling_Date))

It would have values like this:

Bottling_Date = 1/1/2009

month_life = 24

Exp_Date = 1/1/2011 based on the calculation

While this works fine in FileMaker and the correct date is displayed, it is unstored and calculated on the fly and cannot be stored due to the external reference to Master Products.

For some reason when I pull this data via ODBC into a label layout, it loses the additional time added to the date, so my Exp_Date would be 1/1/2009 on the label (ARGH!)

At this point the only semi-solution I see is to make a shelf_life field in the Packaging database that is a lookup, but then that lookup field won't get updated if it changes in the Master Product file without manual intervention in the Packaging database to re-lookup that field.

Any suggestions? I'm open to ideas : )

-=>Chris

1. Re: ODBC Calculated Date Problem

csandoval Jun 11, 2009 1:39 PM (in response to csandoval)

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?

-=>Chris
Like Show 0 Likes(0)

Actions
2. Re: ODBC Calculated Date Problem

mrvodka Jun 11, 2009 2:54 PM (in response to csandoval)

csandoval wrote:
when I pull this data via ODBC into a label layout
Could you elobaorate on this?
Like Show 0 Likes(0)

Actions
3. Re: ODBC Calculated Date Problem

csandoval Jun 11, 2009 3:05 PM (in response to csandoval)

Certainly!

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)
Clone: Clone
__________________________
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

-=>Chris
Like Show 0 Likes(0)

Actions
4. Re: ODBC Calculated Date Problem

csandoval Jun 11, 2009 4:08 PM (in response to csandoval)

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!

-=>Chris
Like Show 0 Likes(0)

Actions
5. Re: ODBC Calculated Date Problem

mrvodka Jun 11, 2009 5:54 PM (in response to csandoval)

Did you set up the System DSN on the box where FileMaker Server is being hosted?
Like Show 0 Likes(0)

Actions
6. Re: ODBC Calculated Date Problem

csandoval Jun 12, 2009 9:56 AM (in response to csandoval)

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?

-=>Chris
Like Show 0 Likes(0)

Actions
7. Re: ODBC Calculated Date Problem

mrvodka Jun 12, 2009 2:55 PM (in response to csandoval)

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.
Like Show 0 Likes(0)

Actions
8. Re: ODBC Calculated Date Problem

csandoval Jun 14, 2009 2:25 PM (in response to csandoval)

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.

-=>Chris
Like Show 0 Likes(0)

Actions
9. Re: ODBC Calculated Date Problem

mrvodka Jun 15, 2009 8:23 AM (in response to csandoval)

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?
Like Show 0 Likes(0)

Actions
10. Re: ODBC Calculated Date Problem

mrvodka Jun 15, 2009 9:02 AM (in response to csandoval)

Do you have Server or Server Advanced? Only Server Advanced gives you an ODBC option between the two.
Like Show 0 Likes(0)

Actions
11. Re: ODBC Calculated Date Problem

csandoval Jun 15, 2009 10:08 AM (in response to csandoval)

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.

-=>Chris
Like Show 0 Likes(0)

Actions
12. Re: ODBC Calculated Date Problem

mrvodka Jun 15, 2009 11:12 AM (in response to csandoval)

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.
Like Show 0 Likes(0)

Actions
13. Re: ODBC Calculated Date Problem

csandoval Jun 15, 2009 3:00 PM (in response to csandoval)

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...

-=>Chris
Like Show 0 Likes(0)

Actions
14. Re: ODBC Calculated Date Problem

mrvodka Jun 16, 2009 8:57 AM (in response to csandoval)

I dont know what to tell you. I replicated your scenario. I used an ODBC connection to FileMaker Server Advanced 9.0.3.325 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.
Like Show 0 Likes(0)

Actions

Go to original post