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

    ODBC Calculated Date Problem

    csandoval

      Title

      ODBC Calculated Date Problem

      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
            

          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

          • 2. Re: ODBC Calculated Date Problem
            mrvodka
              

            csandoval wrote:

            when I pull this data via ODBC into a label layout


            Could you elobaorate on this?


            • 3. Re: ODBC Calculated Date Problem
              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

              • 4. Re: ODBC Calculated Date Problem
                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

                • 5. Re: ODBC Calculated Date Problem
                  mrvodka
                     Did you set up the System DSN on the box where FileMaker Server is being hosted?
                  • 6. Re: ODBC Calculated Date Problem
                    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

                    • 7. Re: ODBC Calculated Date Problem
                      mrvodka
                        

                      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.

                      • 8. Re: ODBC Calculated Date Problem
                        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

                        • 9. Re: ODBC Calculated Date Problem
                          mrvodka
                            

                          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?

                          • 10. Re: ODBC Calculated Date Problem
                            mrvodka
                               Do you have Server or Server Advanced? Only Server Advanced gives you an ODBC option between the two.

                             

                             

                            • 11. Re: ODBC Calculated Date Problem
                              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

                              • 12. Re: ODBC Calculated Date Problem
                                mrvodka
                                  

                                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.

                                 

                                • 13. Re: ODBC Calculated Date Problem
                                  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

                                  • 14. Re: ODBC Calculated Date Problem
                                    mrvodka
                                      

                                    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.