Using variables in a find??

[RwG]

I am New trying to learn FileMaker.

I am trying to write a script that will list calls for a particular shift on a particular day. Shifts are 24 hours long and run from 7:00 to 7:00 (6:59).

I have a menu option with a drop down calendar where the user would select a date and then click a get button.

Here I am trying to run a script that will perform a find and display all records for that day and shift.

I have been trying to use two variables $$StartShift and $$EndShift.

$$StartShift = DateEntrered, 07:00:00

$$EndShift= DateEntrered+1, 06:59:59

The script then tries to run a find on CreateDate with $$StartShift…$$EndShift

I can’t get it to work??

Any ideas??

[bruceR]

You seem to be leaving something out. The calculations you describe are not possible. Can you post a copy of your file or the real, exact and complete text of your script?

Edited October 26, 2010 by Guest

[RwG]

Leaving something out is probably a result of not knowing exactly what I am doing???

How should I be doing it?

How would I post a file?

[bruceR]

Leaving something out is probably a result of not knowing exactly what I am doing???

How should I be doing it?

How would I post a file?

We can't know what you're doing until we can see your file.

When you reply here, you will see, a little ways above the "Add Post" button, that there is a link "Manage Files".

Click that and submit your file.

Note that you need to zip (compress) the file before uploading it.

[RwG]

Do you need the data in the file or just and empty FileMaker database?

Actual data is a problem due to HIPPA confidentiality regulations. I will try remove confidential fields and include only a week or so of data.

[bruceR]

Clone is fine.

[RwG]

Uploaded File

TEST_CALLS.zip

[bruceR]

Two problems.

1. You did not go to the layout that actually holds the Blotter date so your variables were empty. (You could have seen this if you are using FileMaker Advanced with the debugger)

2. You need to quote the ... characters.

Modified script:

Go to Layout [ “BlotterDate” (BOOTTER_DATE) ]

Set Field [ BOOTTER_DATE::StartShift;

Timestamp ( BOOTTER_DATE::BLOTTER_DATE; Time ( 7; 0; 0)) ]

Set Field [ BOOTTER_DATE::EndShift;

Timestamp (BOOTTER_DATE::BLOTTER_DATE + 1; Time ( 6; 59; 59)) ]

Set Variable [ $StartShift; Value:BOOTTER_DATE::StartShift ]

Set Variable [ $EndShift; Value:BOOTTER_DATE::EndShift ]

Go to Layout [ “BLOTTER” (INC01) ]

Enter Find Mode [ ]

Set Field [ INC01::CreateDateTime; $StartShift & "..." & $EndShift ]

Perform Find [ ]

[bruceR]

Actually regarding go to layout I see that your button on the menue layout was OK; but the script is available from anywhere and I was using it from the "Blotter" layout.

[RwG]

Thanks,

That works!!!!!!!!

How do I get to learn these things?

can you recommend a good book??

Edited October 26, 2010 by Guest

[RwG]

I have FileMaker 11 Advanced. Is the debugger something I need to buy?

[bruceR]

Nope, you've already got it. Are you using it? Seems not. Learn to use the debugger and data viewer.

[RwG]

A couple of questions;

I receive fixed width txt files from an outside entity. Three different file types each containing one record of data. Every five minutes, hundreds per day.

Whoever created the files were not consistent in their format. Sometimes dates are delimited with “/” sometimes not. Sometimes “MM/DD/YYYY” Sometimes “YYYYMMDD”. Same problem with times, sometimes “HHMMSS” and others are “HH:MM:SS”. Different formats in the same record however the fields in the record are consistent.

What I have been doing is combining the txt files into one file and then using ACCESS to parse the fixed width fields and create an excel spreadsheet that I can import into FileMaker.

I need to create a time stamp field for comparison. What I have been doing is creating a calculation field using the various date & Time fields.

Date & " " & Hour & ":" & Minute & ":" & Second

This populates the field with a number. Then I change to type to a timestamp and thus can display the timestamp. This is a huge time-consuming manual process that needs to be done regularly as new records are added.

1. Is there a way to parse a fixed with txt file directly into FileMaker?

2. Is there a better way to create a timestamp field automatically and automate the process?

[comment]

1. Yes and no. You can import the entire record into a text field and use auto-entered calculations and/or calculation fields to parse the data.

2. Probably, but I didn't understand how you "create a time stamp field for comparison" and how do you determine the correct date when the format is unknown.

[RwG]

#1-

I am trying to “parse” the field in the table (Table-1) that contains txt data and write to a second table that I created with all the data fields needed (Table-2).

(Table-1)

Txt data is in one field

Multiple records.

(Table-2)

Multiple fields

Zero Records

I get an error that there is no relationship.

How do I go about reading Table-1 and creating/populating Table-2?

#2-

The format of a field in the file is known; however the format is different for different fields. (In the Same record)

Below is an example of a record received.

The first date time field is received as “20100913042440” meaning September 13, 2010 at 04:24:40 AM.

The next date time field is received as “09/13/2010 02:52:25” meaning September 13, 2010 at 02:54:25 AM.

INC02 1.00 20100913042440 1 CAD BF BC100051759 PBF8 09/13/2010 02:52:25 09/13/2010 02:52:33 09/13/2010 03:15:44 09/13/2010 04:14:35

The first date time field always has the format “YYYMMDDHHMMSS”

And the second date time field always has the format “MM/DD/YYY HH:MM:SS”

Etc.

Edited October 29, 2010 by Guest

[comment]

I am trying to “parse” the field in the table (Table-1) that contains txt data and write to a second table that I created with all the data fields needed (Table-2).

I am not sure why this is necessary. You could import the data directly into your final table and parse it there. If you prefer to import into a "pre-processing table" first, you need to import again from this table into the final table.

The first date time field always has the format “YYYMMDDHHMMSS”

And the second date time field always has the format “MM/DD/YYY HH:MM:SS”

The first timestamp can be parsed out as =

Timestamp (

Date ( 

Middle ( text ; 16 ; 2 ) ;

Middle ( text ; 18 ; 2 ) ; 

Middle ( text ; 12 ; 4 )

) ;

Time (

Middle ( text ; 20 ; 2 ) ;

Middle ( text ; 22 ; 2 ) ; 

Middle ( text ; 24 ; 2 )

)

)

I believe the other "fields" match your local format, so it's only a matter of using Middle() to extract the entire string into a Timestamp field (or a Calculation field with Timestamp result).