Jump to content

Tundraboy

Members
  • Content Count

    13
  • Joined

  • Last visited

Community Reputation

0 Neutral

About Tundraboy

  • Rank
    novice
  1. 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
  2. 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 drive
  3. 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
  4. 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 reecog
  5. 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 incl
  6. 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
  7. 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 summ
  8. 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 ticke
  9. 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, tab
  10. 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
  11. 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 b
  12. Thank you, mr_vodka (prefer Scotch myself). Everyone else's code always looks more elegant than mine. Bob Dunn
  13. 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 K
×
×
  • Create New...

Important Information

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