Jump to content


  • Posts

  • Joined

  • Last visited

kelbantaemi's Achievements


Apprentice (3/14)

  • First Post
  • Collaborator
  • Conversation Starter
  • Week One Done
  • One Month Later

Recent Badges



  1. YES!...YES... YES! Works like a champ. Thank you so much.
  2. Genx, YOU ARE THE MAN! I think that will work, have not had the opertunity to test the completed SQL statement and run a query, but the FM Day and Month conversions work. Will fully implement this in a script shortly, and let you know.
  3. Genx, I do not have FileMaker Dev/Adv, only have FM 7 Standard version. so do not have luxury of using custom functions I believe.
  4. Yes, I tried that but kept getting a "ODBC missing operator" error. But after closer scrutiny I think it has to do with Sql is looking for 2 digits on the month(mdf) and Day(mdf). Sql wants to see {ts '2008-10-02 00:00:00'} but my expression Year(mdf)&"-"& Month(mdf)&"-"& Day(mdf)" " "&"00:00:00 is returning {ts '2008-10-2 00:00:00'}. If this is the issue how do I now control for Day and Month place holders. Am I making this more complicated then it needs to be. Or is this always sticky when it comes to ODBC, Filemaker and comunicating to Sql.
  5. New issue now involving SQL 2005 data format. Im importing data from a Sql 2005 database based on a time stamp field. If I duplicate a simple query in crystal 9 the WHERE clause reads ("Material_Trans"."Material_Trans_Date">={ts '2008-10-01 00:00:00'} AND "Material_Trans"."Material_Trans_Date"<{ts '2008-10-01 00:00:01'}) Which gives me all records with a date of 10/01/08. I can not find a way to pass this date in the given Sql format to make the query work. I have been able to make it work in an access database by formatting my Filemaker field like this #10/01/2008# and passing this to my Sql statement I am really under the gun now all our mission critical reports will not run since converting from an access database to Sql 2005
  6. I think I figured out one way to do this, however not sure if there is a better way. All my existing Access queries which now need to be updated to read SQL 2005 were written to a calculated concatenated field containing my ""&" from within the import record script. So now I added a global text field were I hand recode my SQL 2005 query substituting all the (') with (")...which is going to be a pain, then update the import calculated field with the concatenated reference to g_SQL & variable_field. Oh, the variable field is also a calculated field to insert any required syntax to handle text or number formatting. So is there a better way or is this it?
  7. We recently migrated from an Access database to SQL 2005. When comparing the syntax I see queries are now different. Access Example < SELECT `Customer`.`Customer`, `Invoice_Header`.`Open_Invoice_Amt`, `Invoice_Header`.`Due_Date`, `Invoice_Header`.`Document_Date`, `Customer`.`Name`, `Customer`.`Sales_Code`, `Address`.`Status` FROM (`Customer` `Customer` LEFT OUTER JOIN `Invoice_Header` `Invoice_Header` ON `Customer`.`Customer`=`Invoice_Header`.`Customer`) LEFT OUTER JOIN `Address` `Address` ON `Invoice_Header`.`Customer`=`Address`.`Customer` WHERE `Address`.`Status`='Active' AND NOT (`Customer`.`Sales_Code`='Edmar' OR `Customer`.`Sales_Code`='EMI') ORDER BY `Customer`.`Sales_Code`, `Customer`.`Customer` SQL 2005 Example < SELECT "Customer"."Customer", "Invoice_Header"."Open_Invoice_Amt", "Invoice_Header"."Due_Date", "Invoice_Header"."Document_Date", "Customer"."Name", "Customer"."Sales_Code", "Address"."Status" FROM ("PRODUCTION"."dbo"."Customer" "Customer" LEFT OUTER JOIN "PRODUCTION"."dbo"."Invoice_Header" "Invoice_Header" ON "Customer"."Customer"="Invoice_Header"."Customer") LEFT OUTER JOIN "PRODUCTION"."dbo"."Address" "Address" ON "Invoice_Header"."Customer"="Address"."Customer" WHERE "Address"."Status"='Active' AND NOT ("Customer"."Sales_Code"='Edmar' OR "Customer"."Sales_Code"='EMI') ORDER BY "Customer"."Sales_Code", "Customer"."Customer" All my solutions in FM scripts are import records requests. I see a problem when using calculated Dynamic WHERE requests. When I recode a FM query Example < "SELECT `Material_Location`.`Material`, `Material_Location`.`Location_ID`, `Material_Location`.`On_Hand_Qty` FROM `Material_Location` WHERE `Material_Location`.`Material` =" & g_PartVariable > FM throws up an error telling me it can not find field…in this appended recoded example as < "SELECT “Material_Location”.”Material”,> can not find Material_Location. I have to say from the get-go I struggled with syntax issues from the beginning even when my queries were to Access tables. In fact My work around has been to design my table requirements in a crystal 9 report and copy and paste the SQL query from crystal to FM and make minor adjustments. I believe my problem lies with the SQL 2005 use of quotes. I am afraid this is going to be a daunting task to resurrect my FM solutions. Any help will be appreciated
  8. Yes, Here is everything I got, relating to converting data to a barcode 128 format. Bar128.zip
  9. I am working on a solution that exports from a series of access tables via ODBC to FileMaker. FileMaker is creating a report basically showing a production routing listing work centers and an operation sequence number. The operation sequence number is represented as a bar code (barcode 128AB). Just representing the data as a given barcode will not work, when scanned. It appears as though the data is converted with a control character at the beginning and a calculated check sum value again converted to ASCll code, at the end. I was given a utility program and a file used to import into either excel or an access table that via Visual Basic scripting converts the data. The utility program will help to check a sample value. Also included is a series of conversion charts listing the ASCII code variables. I want to use the FileMaker printed Report list of operation sequence numbers in barcode128 so we can scan them and have our data collection software recognize them. The report out of our existing software program does not meet our needs. First I need help evaluating the best solution. 1. Can I use the above VBscript information I have to do the data conversion externally, and then bring the data over. 2. Can I use the data conversion information to have FileMaker do the conversion. Or am I biting off more then I can chew . Of course everything in my solution works great, and looks great, except for the bar coding issue, which brings me to a screeching halt. Any assistance will be greatly appreciated.
  10. I'm not sure if this is the right forum, if not please direct me to were I should post this. My problem is this, I have a FM7 File that needs to periodically update via ODBC, some of the tables require existing records be deleted and imported again. Solution has worded well for months. But now the record updates are VARY SLOW (aprox time is 10-15mins) It’s not just the ODBC it is the record delete step that is slow. It will also hang filemaker from time to time. Originally time was aprox 1 min or less. File size is consistently about 1 meg, record import is about 10,000 records consisting of 4 to 9 fields depending on the table of which there is a total of 4. The Machine is windows XP SP2. I can run the same solution from my machine which is a carbon copy of the other machine, and solution runs as normal. Where might I begin to look for problem.
  11. I’m working on a solution and I need some suggestions as to where to start. I have a customer base of about 150 accounts. And a product line of about 3,000 items. The customer accounts are broken down into four groups to identify a specific pricing group, A-D. Each group has its own selling price, across all products offered, identified in each customer record, i.e. account number, name, address, and pricing code. What I want to do is to be able to reference, at point of sale a product portal specific to that customers pricing structure. Where my sales staff can identify the customer and the portal would list all products with the appropriate selling price. My question is which would be better; to have one product table for all four pricing structures, or have four separate product tables for each pricing structure. In either scenario there would be a master product table holding other relevant data i.e. piece weight, skid quantities etc. For either scenario I need a little direction and assistance in how to build my relationships, and tables, I have gotten myself stuck. I’ve been taking some stabs in the dark, but now I think I have maybe made this more complicated then need be and sure could use some redirection. I know there is a lot more to be done to really make this a working solution, but if I could just get some success with the basic premise I may regain my focus.
  12. I have been looking for a solution in FM7 to keep printer selection consistent for a given layout. I have installed appl. and tried an external autofil script but can not get it to work. Has anyone had any success with this option? I have included the thread where I found the script, from Slimjim http://www.fmforums.com/forum/showtopic.php?tid/170285/post/180139/hl/autoit/#180139 I have tried the other offerings on this link also, and have had no success. My printers are all a shared resource on our server, and are listed in "print setup" as an UNC path i.e. servernameprinter1, servernameprinter2...., wondering if this is my problem. Any help here would be greatly appreciated.
  13. I can get one record to print using a variable counter within a print script, to print the required number of labels. But am having trouble tring to combine it with another one to loop through each record. Can't find the right mix. Can someone help me out here.
  14. Here is my problem: I created a FM7 file to retrieve data via ODBC from a series of Access tables, the solution works well on all my Microsoft Windows XP machines. However I have one Windows 98 machine still in service on our network. I need to run this solution on that machine. I do not have FM7 Developer, and understand it can compile FM7 files to a stand alone application. If this is correct this may be my solution. I do not know how difficult this would be to compile, nor if this is an appropriator request to make. But it never hurts to ask, so here I go. Would someone be willing to look at my file and possibly recompile it to a stand alone application . Or if there is another direction I should go, direct me. Any help here would be appreciated. I am under a significant amount of pressure to get this one solution going, without additional software investment, or lost time. A working solution here would greatly increase my bid for additional investment in such things as FM Developer, and continued hardware investment TranferLabelTest.zip
  15. Thanks Matley, As luck would have it I figured it out just as you have stated in your post not more then a day or so prior to your message. I am using a text calculation for the SQL statement, and a field refrance to an imput field for the user variable. My solution appears to work in my simple test FM Table. I am unclear on your use of the global field however.
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.