I have some CSV files with dates in format d/m/yyyy which when imported enter as invalid dates. How can I change this? How can I change FileMaker's default date format to d/m/yyyy?
While you could probably change the setting on computer and/or file to this date option, import the data, then change back. It will probably make more sense to import such data into a text field, then parse the text into a real date either as part of the import via an auto-enter calculation or as a "post import" update of the data via a script or manual batch update process. (replace field contents or a looping script can do such a batch update.)
If / is the delimiter, you can do this as a way to process the data as part of the import. (But importing then updating, even importing into a table set aside solely for post import processing before a second import moves the data often makes sense for importing data into FileMaker in a controlled fashion.)
Match up the csv date field with a global text field. Define the following auto-enter calculation on the date field:
Let ( DateList = Substitute ( GlobalFieldHere ; "/" ; ¶ )
Date ( GetValue ( DateList ; 2 ) ; GetValue ( DateList ; 1 ) ; GetValue ( DateList ; 3 ) )
This requires that you enable auto-enter options during import--which is not always possible.
Note that a batch update done to the data after import could use much the same calculation, but referring to a local text field in the record into which the date was imported as text rather than a global text field.
how does one do a "an auto-enter calculation " at import?
You define the auto-enter calculation in field options like any other. Then when you finish mapping fields for your import, a small dialog opens that asks about repeating fields and auto-enter options. you use that dialog to enable auto-enter options. Note that this enables all auto-enter options and thus this may not work due to other fields auto-entering data during import that should not be auto-entered during the import. Thus there are other options that also manage this issue.
BTW, what are repeating fields? Can you offer an example?
In current FileMaker solutions, repeating fields are something to avoid except for certain very specific special circumstances.
Once upon a time, FileMaker was a flat file database. All data existed in a single file in a single table. to get any kind of Multiple instances of the same data grouped with the main information--what we now do with a parent table and a related child table, you could (and still can) define a field as a repeating field. Such a field can store up to a predefined number of repetitions of the same field, kind of an array. Unlike a related table, you have a fixed number of repetitions possible, plus there are other limitations that make a related table nearly always the better option. But you can look at storage options in Manage | Database to see how such are defined and you can see settings on the Inspector's data tab that control which repetitions for a field are currently visible.
Plus, script steps that modify data in a field, such as set field, have an extra spot where you can specify the repetitions that will be visible for a given field object on the layout.
Is there a way to debug custom user defined functions like the one for formatting dates? Like going step by step or looking at variable values as they change during execution of the function?
Get FileMaker Advanced and use the data viewer.
You can copy the expression into the data viewer as a watch expression and get an immediate result. For complex calculations, you can even break down the calculation into sections to check each intermediate value in the data viewer.
Retrieving data ...