Jump to content

Creating a report within two dates


This topic is 2620 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.  

  • Like 1
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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: …

Link to comment
Share on other sites

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 :cool: 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.   :yep:

 

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 by LaRetta
  • Like 1
Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.   :laugh2:

Link to comment
Share on other sites

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?  

  • Like 1
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.  :getlost:

Link to comment
Share on other sites

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.  :getlost:

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.

Link to comment
Share on other sites

This is my version of the script LaRetta supplied. I removed the other custom dialog as it isn't something I need. post-111022-0-15853000-1401387441_thumb.

 

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

Link to comment
Share on other sites

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

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.  

  • Like 1
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

post-55421-0-26005700-1401401599_thumb.j

Edited by Lee Smith
Added a screen shot
Link to comment
Share on other sites

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.   :tongue:

Link to comment
Share on other sites

 

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.

 

 

:idunno:

 

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.  

Link to comment
Share on other sites

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.

post-62898-0-88884100-1401557648_thumb.p

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2620 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

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