Creating a report within two dates

[ConorH16]

Ok, I am trying to create a report, finding the records between two dates.

 

I am creating a script which will take me to the desired layout and then show a custom dialog where I can enter the start and end dates.

 

The script is as follows:

 

Go to Layout ["HorseCostReport" (Main Charges Table)]

Sort Records [Restore; No dialog]

Go to Record/Request/Page [First]

Enter Find Mode[]

Show Custom Dialog ["Specify Date Range'; Main Charges Table::GlobalStartDate; Main Charges Table::GlobalEndDate]

Set Error Capture [On]

Set Field [Main Charges Table::SearchDate; Main Charges Table::GlobalStartDate & "..." & Main Charges Table::GlobalEndDate]

Perform Find []

 

It's not working so I know i've done something wrong...

Can anyone tell me what I am doing wrong?

 

Thanks,

Conor.

[bruceR]

1. You don't describe what "not working" is.

2. There is no point in sorting or going to first record before a find operation.

3. You'd know the answer if you completed the training you said you were going to pursue.

[ConorH16]

No need to be rude. I came on here to ask for help not to get stupid pointless answers like that! Some 'consultant' you are...

[LaRetta]

Hi Conor,

 

Try an approach like this (pseudo-script) below:

Show Custom Dialog ] "Specify Date Range" ; GlobalStartDate ; Global EndDate ]
If [ not ( GlobalStartDate + Global EndDate )  // both values are empty ] 
Show Custom Dialog [ "You did not enter dates.  Please try again." ]
Exit script
End If
# ready to proceed ...
Freeze Window
Enter Find Mode [ uncheck pause ]  
# Entering find mode before switching layouts is more *efficient
Go to Layout [ HorseCostReport ]
Set Field [ SearchDate ; GlobalStartDate & ".." & GlobalEndDate ]
# two periods or three will both work here to create the ellipsis ... I like to use two.  If no start date is entered, system assumes all prior records up until the EndDate and if no EndDate then system assumes all records greater than StartDate.
Set Error Capture [ On ]
Perform Find [ ]
If [ not Get ( FoundCount )  //  no records found ]
Go To Layout [ original layout ]
Show Custom Dialog [ "No records found" ]
Exit Script
Else
# records are found so sort them
Sort Records [ Restore ; No Dialog ]
End If

* When you switch to a layout, records are downloaded for that table but why download records when you are going to perform another find and change the found set anyway?  By entering Find Mode before going to a layout, you stop the download of any records until after you perform the find.

 

If you want to prohibit an open-ended find (where Users are allowed to leave one global or the other empty), you can insist that values be entered in both fields.  That is a business decision you will have to make.  

[LaRetta]

I should mention that it would be best to hold off on creating reports until you are sure your structure is correct; something we are working on at present using Comment's suggestions on your other post.  Ideally, you would not have a SearchDate but rather it named the activity date of the action in the table you are searching.

[ConorH16]

Thanks for that LaRetta! I just thought I had better use my time wisely as I could not go any further with my structure until I got professional help.

 

Just forward thinking on my part but I get what you mean.

 

Thanks!

Conor

[bruceR]

Regarding elIipsis:

 

I think you mean two periods:  ..

 

Or you can use three periods:  ...

 

An actual ellipsis can be used in range finds, it is a single character, with the appearance of three periods, option-semicolon: …

[LaRetta]

Oh for sure ... when I was new to FM (although I had years of relational theory and database experience), I just began creating scripts.   I presented this script because A) You can learn from it for future without waiting (as you say) and others reading this thread or searching for similar problems can use it.

 

Everyone has a different style ... some dive in; some read the manual first ... I was a DIVER also.  

 

Once your structure is sound, you'll be able to rock 'n roll.

Bruce, in FileMaker speak, we call them  'two periods between a range or three periods between a range' ellipsis as well but I have changed my script to say periods to please you.

Edited May 28, 2014 by LaRetta

[bruceR]

They aren't two ellipsis periods. They are just two periods.

 

The FileMaker find mode "Operators" dropdown inserts a single ellipsis character

 

# two periods; or three; or an actual ellipsis character (option-semicolon)

[LaRetta]

Ok Bruce.  It seems you have a slow day and you've again sought me out to pick on. However, I'm not in the mood ... I'm having far too good a day to engage you.  

[LaRetta]

BTW, I see that, when within code I use strikethrough, it does not strike it out.  I had crossed out the ellipsis and changed it ONLY to periods to meet with your approval.  It seems that type of formatting will not hold.  So I will REMOVE the word ellipsis from the code. 

So now can we get off of picking on me and get back to helping the client?

 

All red text?  REALLY?  

[ConorH16]

Ok LaRetta. I have tried your script and it works up until I switch to the HorseCostReport layout and it shows all of the records instead of just the ones within the dates specified.

 

Any other ideas to what I might be doing wrong?

[LaRetta]

You have FMPA (Advanced) so run the script stepping through debugger and tell me exactly where it breaks.   When it switches to the HorseCostReport, does it set the Search field with the date range properly?  Also print the script to pdf, zip and attach it for review.

 

And that SearchDate ... is that the date of the charge's activity?  It should not be a global and there should be many records in the HorseCost table which match within the date range you specify.  

 

Otherwise you will need to post your (zipped) file.  I realise you hesitate to post your file.  Get over it.  Nobody cares what your file looks like. 

[Lee Smith]

Otherwise you will need to post your (zipped) file.  I realize you hesitate to post your file.  Get over it.  Nobody cares what your file looks like. 

I agree with LaRetta, and have suggested this to you at least once before.

 

Read this about why and How to add an attachment to a reply.

[ConorH16]

This is my version of the script LaRetta supplied. I removed the other custom dialog as it isn't something I need. 

 

Not too sure how to run the debugger as it doesn't seem to be visible on my screen... 

 

I have attached a much simpler version of my solution so you don't get confused as to which part i'm having trouble with.

 

The cost report button doesn't seem to do what the scripts meant to - I'm doing something wrong, again.

 

Also, If you can - Could you tell/show me how I would only view one horses record on both the cost and history report layouts at a time. I only wish to see records related to one horse at a time.

 

Thanks!

Conor

 

Livery New 2.zip

[Lee Smith]

Go to Menu Bar >> Tools >> Script Debugger and open it.

At the same time, open the “Data Viewer” Menu Bar >> Tools >> Data Viewer

 

Run your script and step through your Script Steps.

Debugging Scripts here

FileMaker Help on Using the Data Viewer here

[bruceR]

It's the structure problem that has been described to you previously; and which has not been implemented.

There is no chargeTypes table and the charges table is too complex.

[Lee Smith]

Are you working on these files while there are being served?

[ConorH16]

Bruce, I am working on it.

 

What do you mean, Lee? There isn't a tools menu on my screen... hmm...

[LaRetta]

I am working with Conor on the structure (for the other thread) but I've said I  won't have a chance to look at the files until later today.

 

Conor wanted to keep busy by learning the reports piece.  No problem, Conor, we'll get 'er done later.  :-)

 

Hey, Lee, thanks for showing him debugger.

 

As Michael indicated originally, I won't know about the charges until I review the data.  Then this report should fall into place so you can keep this script as is.  

[ConorH16]

Cheers LaRetta! I'll leave it as it is and see how it turns out.

[Lee Smith]

What do you mean, Lee? There isn't a tools menu on my screen... hmm...

Are you sure you are using the Advance and not the Client of FileMaker?

[ConorH16]

Yes. FileMaker Pro Advanced 13.

[LaRetta]

Then if you look across the top when FM is open, to the right of Scripts, is Tools.

[ConorH16]

Hmm... 

[Lee Smith]

Something isn’t right then. Try reinstalling FileMaker and see that changes things. Also be sure you are using the most current version 13.0v3 (04-17-2014)

 

Here is what it is supposed to look like.

Edited May 29, 2014 by Lee Smith
Added a screen shot

[LaRetta]

It's the structure problem that has been described to you previously; and which has not been implemented.

There is no chargeTypes table and the charges table is too complex.

 

Actually that is not it at all.  The reason the script isn't working is because there is a Show All Records script trigger on the report layout.  And in the script, the field being searched is SearchDate (which is global text which is used only to display the search range at the top of the report) when it should be Date (the date of each charge in that table).

 

With those two things fixed, the report works as it should.  

 

As for the structure, that is being addressed as suggested by Comment on the other thread.  

[bruceR]

I don't see evidence that structure is being addressed.

[LaRetta]

 

I have attached a much simpler version of my solution so you don't get confused as to which part i'm having trouble with.

 

 

 

If you read the thread, it has clearly been conveyed repeatedly that the structure is being addressed in the real file.  THIS ISSUE involves a problem with a find, it is a learning exercise, and the structure does NOT depend upon this find working properly.

 

This really is getting tiring, Bruce.  

[bruceR]

Since you did not describe what "not working" is, it was necessary to see the file to find out.

(See line 1, reply 1)

 

"Not working" is that the layout always shows all records and disregards the find script.

 

The layout always shows all records because that's what you told it to do:

You have a script trigger, on record load, that shows all records.

Any time you touch a record, it will show all records.

When you turn this off, and modify the script, the find script works.

[bruceR]

LaRetta: Regarding text color:

The truth is more mundane.

I copied your ellipsis phrase, pasted it into my reply, edited it to provide what I thought was a more accurate description.

I did not notice your original text color and did not change it.

But in retrospect - I can see that your original text was red, and therefore mine was also.

There was no further intent or message in the text color.

[Lee Smith]

For late followers of this topic, we are talking about Post 9 here

 

Hi Bruce, it is IMHO a bug with this site’s software, and I fight it constantly. I was wondering why the RED text, and now that I look back, I see the red in the ellipse.