How to get user input for date range in a report

[kgasman]

I wanted to create what I thought would be a simple report. I created a database for my wife's practice so she can take client notes and track billing. The notes and billings are in related tables to the client personal data.

The report was to show all successful credit card charges in a date range selected by the user. I wanted the report to group by name and within the name group sort by date. It would show a subtotal for each name and a grand total at the end of the report.

So I created a layout in the "ClientBilling" table. In the "ClientBilling" table I have a field "FullcardName" which is a calculation of the fields LastNameonCard & ", " & FirstNameOnCard. I have a field "dateofcharge", "amountcharged", "amountrefunded", "balance" which is a calculation of "amountcharged"- "amountrefunded", "balancesubtotal" which is a summary field = Total of Balance. Lastly I have a memo Field set to “Payment successful” when a successful charge is made.

I put the field "FullcardName" in the subsummary by Fullcardname(leading) part

I put the fields "dateofcharge", "amountcharged", "amountrefunded", "balance" in the body part

I put the field "FullcardName" in subsummary by Fullcardname(Trailing) part along with the summary field "balancesubtotal"

In the Trailing Grand Summary part I put the summary field "balancesubtotal"

I created the following script

Go to Layout [ “CompletedCharges” (ClientBilling) ]

Adjust Window[ Maximize ]

Perform Find [ Specified Find Requests: Find Records; Criteria: ClientBilling::Memo: “Payment successful” ]

[ Restore ]

Sort Records [ Specified Sort Order: ClientBilling::Fullcardname; ascending ClientBilling::DateofService; ascending ] [ Restore; No dialog ]

Enter Find Mode [ ] [ Pause ]

Perform Find [ ]

Enter Preview Mode[ Pause ]

Go to Layout [ “Intake Form-start” (ClientDatabase) ]

Adjust Window[ Resize to Fit ]

Enter Browse Mode

Halt Script

I can't figure out how to let the user define the date range. The enter find mode script step I thought would allow the user to enter a date range and the perform find script step would constrain the found set to the date range input, but it doesn't work

[bruceR]

Well for one thing, the steps are out of order. You have the sort before you've found the records. You mention the "constrain" option but that is not the script step you have selected.

Probably more like this:

Go to Layout [ “CompletedCharges” (ClientBilling) ]

Adjust Window[ Maximize ]

Enter Find Mode [ ] [ Pause ]

Set field [Criteria: ClientBilling::Memo: “Payment successful”]

Perform Find [ ]

Sort Records [ Specified Sort Order: ClientBilling::Fullcardname; ascending ClientBilling::DateofService; ascending ] [ Restore; No dialog ]

Enter Preview Mode[ Pause ]

Enter Browse Mode

Go to Layout [ “Intake Form-start” (ClientDatabase) ]

Adjust Window[ Resize to Fit ]

[kgasman]

Go to Layout [ “CompletedCharges” (ClientBilling) ]

Adjust Window[ Maximize ]

Enter Find Mode [ ] [ Pause ]

Set field [Criteria: ClientBilling::Memo: “Payment successful”]

Perform Find [ ]

Sort Records [ Specified Sort Order: ClientBilling::Fullcardname; ascending ClientBilling::DateofService; ascending ] [ Restore; No dialog ]

Enter Preview Mode[ Pause ]

Enter Browse Mode

Go to Layout [ “Intake Form-start” (ClientDatabase) ]

Adjust Window[ Resize to Fit ]

So if I understand what you are suggesting...

In the Enter Find Mode script step the user would enter a date range. Then before executing the perform find script step the set field script step adds the memo criteria to the find.

[bruceR]

Yes; though those statements could be reversed (enter find mode no pause; immediately set field; pause (wait for input ) ; perform find.

[kgasman]

Is there a way in this situation to create a found set that excluded criteria.

So if I wanted the found set to be all records in a certain date range where the Field "memo" was not empty or contained the word "error"

[bruceR]

Add step set field memo; "*"

Note that not empty includes the case where field contans "error"; so you really have only one operation to perform.

[kgasman]

First thank you for all your help

I guess I am not thinking about this clearly, which I find when creating searches.

the Memo field is either empty, contains the word "error", or contains a string of nine digits if the transaction was successful. So if I want a report to only show successful transactions I should actually only search for a string of nine digits. There is no way to search for this, is there?