Tundraboy

LaRetta, I'm not using ESS and I'm using 8.5Adv with just a local DSN. Sorry, more info I forgot to give! That might be why I can't find any reference to the to_date() function. I am also unclear if this is to be used inside the SQL statement or elsewhere. This particular test was being done just as a straight import via ODBC not as a script. Bob

LaRetta, It is a proof of concept test. The query is being built to test if it is possible to take only the necessary data from a .dbf file (dBASE IV format). The file can contain up to 100,000 records and I would rather not pull all of that in if possible. This is the reason for the hard coding of a date. FileMaker seems to recognize that the field being used in the query is of type date and displays the date values in the query builder, so it should work in theory. Maybe the ODBC driver is the problem. Currently the ODBC connection is made using the Microsoft dBase driver. Does the date translation usually happen in the driver? Or is FileMaker doing some sort of translation on the fly? Bob

Allison, Yes that is true of SQL queries but this is what is displayed in the FMP SQL Query builder when you use their tools to build the query. I would guess that FM adds the missing punctuation. I used the Query builder because when I used a regular SQL statement with punctuation et al the query failed. Bob

I have been attempting to import data from a set of .dbf files that are served out via ODBC. All is well with straight forward pulls but I have a problem if I want to use a date column in a WHERE statement. The following is created using the SQL Query builder during the import process. SELECT `STUDCAL`.`IDSTUDENT`, `STUDCAL`.`SDAYSTATUS`, `STUDCAL`.`SDAYACTIVE`, `STUDCAL`.`DAYDATE` FROM `STUDCAL` WHERE `STUDCAL`.`DAYDATE` = 2007/09/28 AND `STUDCAL`.`SDAYACTIVE` = 1 The DAYDATE is a value which was taken from the Query builders pull down list, so FileMaker seems to reecognize the value at that point. When the query is run an empty set is returned with no errors. I know that there are matches (I did an import from one of the smaller datasets (18232 records) and found many matches. Any idea as to why this statement did not work?

Fenton, I have a sample of the Attendance Utility attached (I think, haven't done that before here) that should help this dialogue a bit. The "Table1" and "Table2" references in this message thread are the Attendance Utility table and the Attendance_Calc table respectively. The layout for which the information is destined is the Attendance File. This utility is acting as a reporting tool for data from another database (database set actually). Any field names in caps are populated via ODBC from that dataset. There is only one sample student and their attendance data included. Normally there would be from 100 to 500 students and their attendance data. Bob Attendance_Utility_Alpha007_Copy.zip

Field5 is a flag identifying when/if a students enrollment status changes which is directly related to attendance. It is my attempt to identify which records have status changes. Each record in Table2 indicates 1 day of attendance information for the student. So the only quick way I could see to indicate which records had changed status was to create a flag. Bob

Fenton, The intent is to return one result from the related record that is flagged. Using Vaughan's example of Passengers and Baggage tickets, I would be looking for each passenger that has missing baggage. Assume for the moment that only 1 piece per passenger can go missing (insert airline humour here) and that a flag has been set in the record of the missing piece of baggage. The real business logic surounds students and their attendance. and the flag is set on students that have transferred or withdrawn. The layout I am trying to populate is a summary of the attendance. The summary needs to report the status change code when a student has withdrawn. Both tables are currently related using a student identifier number, which is serialized. Table2 "Field5" is a flag which is calculated field (unstored) and Table2 "Field4" is the status code. Because the summary layout is based on Table1, there is a calculated field that is intended to catch these flagged records and return the status code to the field. The Case statement at the beginning of this thread is the calculation for this field. Passengers and baggage or students and attendance, the problem is that I thought the current table relationship was enough to allow the Case statement to identify the flagged record and act only on it when returning a result. What actually appears to be happening is that the Case statement only acts on the first record in the related table. Vaughan has suggested in his reply that another relationship may be needed. If necessary I can post a sample of the database ... could take a bit of cleaning though to prevent privacy issues. Bob

Hmmm. So I am only half correct in my understanding. To user your analogy the problem I have is with the "baggage tickets". I need to return information about one of the baggage tickets not all of the baggage tickets related to the passenger. A portal does not do the trick in this case. What I was hoping to achieve with the Case statement was something similar to this SQL statement: Select field4 from table2 where field5 = 1; If I placed a foreign key in table1 (Passenger ticket) from table2 (Baggage tickets) could the extra identifier be used to single out only those baggage tickets that were flagged (field5)? Bob

Apparently I don't quite grasp how a relationship between two tables works. I have been trying to get a calculation to work but the results are always wrong. If two tables are related by some identifier in a 1:M relationship, and a layout (based on the table with unique identifiers) calls for information from the related table (with multiple instances of the same identifier), why does the following calculation only seem to act on the first record it finds in the related table? Case (table2::field5 = 1; table2::field4; table2::field5 = 0; "N/A") So if table1 has 1 record, table2 has 5 records and record 3 of table2 has field 5 populated with a 1 it would seem I should be able to pull table2::field4 because it has found that record. That does not seem to be the case with my database I only ever seem to have table2::field4 record 1 returned. I realize that there are probably a number of other ways of returning the data from table2::field4 but the problem is my grasp of the theory is apparently wrong in the first place. Can someone tune me in?

Ok ... no takers. Maybe someone can help me understand the relationship so that I can get the calculation to work. This is a 1:M relationship with the primary key of the main table used as a foreign key in the data table. It is my understanding that this should act like a "Find" of related records via this relationship. If this is so, then using a calculation that identifies a particular field with a particular attribute should identify that specific record? So if the relationship finds 130 records with only 1 having the criteria outlined in the calculation it should return results from that record? Please,fill me in if I am way out on this! Bob

I have a reporting tool wiht two (main) tables. One is an attendance summary (Attendance_Utility) with one record per student. The other is a collection of the attendance data (Attendance_Calc) with one record per day per student and typically contains 30000 to 70000 records (number of days * number of students). The tables are related by a students ID (unique in the summary table, many instances in the data table). For each record in the data table there is a daily status indicator. If it changes from the enrolled state to the withdrawn state a flag is set. If the flag is set I should be able to pull the "Status_Change_Code" for that record from the data table to the summary table. I have gone around in circles for 2 days now trying to do what appears to be a very simple thing ... but to no avail. Here is the calculation for the field (Status_Change_Code) in the summary table (Attendance_Utility): Case ( Attendance_Calc::SCC_Flag = 1; Attendance_Calc::SDAYSTATUS; 0 ) SCC_Flag is the status change indicator flag and is 1 or 0 (zero), SDAYSTATUS is the field where the status code resides. SCC_Flag is an unstored calculation and SDAYSTATUS is indexed text. Is it a bad calculation or a bad table relationship? I've tried so many things that I can't sum them all in this post. Bob

Thank you, mr_vodka (prefer Scotch myself). Everyone else's code always looks more elegant than mine. Bob Dunn

I am importing data from external DBF databases into FileMaker for reporting purposes. One particular imported field is text in the DBF but needs to be reported as a number from the FileMaker utility. Two questions for those who are wiser than I: - is it better to try validating or converting the field on/during import or - is it better to leave the imported data and find a way to convert it after import. If this helps the data is mostly numeric, but there are exceptions. The data is a student grade level indicator and the usual exeptions to numeric are Kindergarten (usually KA, KP for AM class and PM class) and Graduated (typically G, GR, GD). The kindergarten indicators need to be changed to a 0 (zero) and the graduated indicators need to be changed to 13. Bob Dunn