invoice question 1

[foster1929]

I'm new to filemaker, can someone help me with script making. in an invoice I would like a field setup to show running totals of each customer that I have in a drop down list, from a certain spand of time [example--Jan 05 thru Dec 05]. then another field to show totals of all customers.

 

[JeanW]

A running total will not help you in this.

You want a total per given customer for a given period.

Look in the manual what summaries can do for you.

In FileMaker you have a wizard to create such layouts, which will guide you through the whole process.
One of the last steps is if you want to make a script for the process. Simply click yes.

HTH

 

[foster1929]

still confused.

 

[JeanW]

Where ?

Can you be more specific about what you want to do or which result you want.

It's all a little bit vague for me.

running totals of customers: which values, the amount, the articles on the invoice ?

then another field to show totals of all customers: is this how many customers or the total invoiceAmount for each customer for a given time ?

I know it's not always that easy to put things in words, a little more detail could give the half of the possible solution.

You're still confused, where, what did you already tried, what was the outcome of it ?

HTH

 

[foster1929]

I have an invoice that I have been using for about 14 months. It has a pop up list of all of my customers.I would like to make a Layout Report to show seperate totals of each customer for a certain period of time [Jan-Dec] and also kept totals for each month.
I hope this is a better description

 

[JeanW]

Yes, it is. Thanks.

But again, I have to point to the summaries chapter in the manual or the online help file.
It will be to long to explain it here.

When you create a layout, FM will give you a few options, choose 'Columnar/List report', then Report with grouped data, include sub or grand totals, make a choice for your fields, organize your records by category, sort your records, etc.

Just before you run the wizard, make a found set of your records (scripted or not) to find the records for your customers. The variable here will be the 'timerange', f.i. in a datefield, a range from jan01 to dec 31.
Keep this foundset and run the wizard. The last step will give you a choice to make a script, check yes and see if the script gives you the desired result.
If not, you will have to tweak the script, or run the wizard once again with different items, untill it gives you the right view.

If you don't have fields with the totals you want, like amount etc. you must create them first.

HTH

 

[foster1929]

JeanW,
Thanks for your help but scrips are difficult for me.

 

[JeanW]

Where do you have problems with scripts ?

 

[foster1929]

JeanW
Could you show me a sample of script that would get me to understand what I am trying to do. I want to have a button that would give the results for 12 months of invoices.I have a drop-down list of customers to choose from and would like the results for each customer.
Thank you for your help

 

[JeanW]

You’re new to FileMaker, so let’s go first to the basic of what Scriptmaker is.

It isn’t a computer language like BASIC, Pascal or C/C++.
Scriptmaker is basically a macro generator. What’s a macro ?
It’s a sequence of steps that run in a linear fashion to automate a process that would otherwise be performed manually.
What you get with Scriptmaker, that you don’t get with other macro languages, is a dynamic environment. Once finished, you can end up with many scripts that create a feature set.
One script can call another script, which can, in turn, call yet another script. You can even use parameters in scripts.
Scripts can also make logical decisions (If statements) and perform repetitive tasks, we call that looping statements)

Scripting is what makes a database solution equivalent to a piece of software.
So, in the truest sense, you are programming when creating functional scripts.

Try first those steps:
This whole thing can be ‘automatic’ with a wizard in FileMaker.

First make a search for the date range, do this first manually on the date field, to have a found set of records.

Layout > New Layout/report > Show record from yourTable, give layout a name > Columnar/List report + Next > (Watch the sample view at the right side), report with grouped data, include subtotals (watch color mark in the sample view) + include Grand Totals + next > Specify Fields (make a choice for the fields you want on the report) + next > Organize record by category (click on fields you want and watch color tack in right sample) + next > sort records (make choice and sort order) + next > specify subtotals + next etc etc. until you reach the last window with Create a script for this report. Click for Create a script and give the script a descriptive name + next > view the report in preview mode + finish.

Look at your layout, go to scriptmaker, navigate to your last script and edit it.
Here you have all the scriptsteps.

An other way to do searches with scripts is to redirect the user to a ‘search screen’, let them fill in the data and perform several scripts to find the data.

This is more work, but you have to do this just once for a table and you can copy this to every other table in the solution.
With those script you will have a nearly bulletproof searchsystem and there will be nearly no chance that the user is left in a screen with f.i. 0 records and no way out of it.

This is longer to explain, so if you want that, feel free.

Search scripts are very difficult to explain without visible example. There are too many 'after search' items to deal with.

It’s not allowed to give email addresses on this forum nor attache sample files, so if you need more info, go to

http://www.prodigyweb.net.mx/pesmerida/Page5.htm

and send a mail. This way we could exchange samplefiles.

 

[foster1929]

JeanW
Thank you for your help.
After doing a FIND for the date range on the customer I did get the results that I was looking for. i was hoping to have a button to give me that info at any time, but I can live with this way.

Thanks again for your help....

Roger

 

[JeanW]

Do you need more guidance to make a script/button for your search ?

A little step by step could help.

 

[foster1929]

yes, if you could show me the scrip. and do i have to do a date range in the find mode each time bebore the button will work. thanks...
roger

 

[JeanW]

Let see if I can guide you to a script and meanwhile give you some tricks....


You already know that a script is a sequence of steps that run in a linear fashion to automate a process that would otherwise be performed manually.

What you want is a script, attached to a button, that will give you a found set of records in a date range, but you don’t want to key in the daterange. (for a start)

We will trigger on the invoiceDate field, which is the date of the invoice.

To make this work you need a text field where you calculate the invoiceDate field to have another output. This output you can ‘tweak’ as much as you like.

Make a calculation, result text field in the invoicetable, along these lines (you don’t have to put this field on a layout, it will work ‘behind the screens’):

DateText_cti =
Case(
invoiceDate = Get(CurrentDate);
"today";
invoiceDate = Get(CurrentDate) - 1;
"yesterday";
invoiceDate = Get(CurrentDate) - 2;
"Last " & DayName(invoiceDate);
"")
& "" &
Case(
WeekOfYear(invoiceDate) = WeekOfYear(Get(CurrentDate)) and Year(invoiceDate) = Year(Get(CurrentDate));
"this week";
WeekOfYear(invoiceDate) = WeekOfYear(Get(CurrentDate) - 7) and Year(invoiceDate) = Year(Get(CurrentDate));
"Last week";
"")
& "" &
Case(
Month(invoiceDate) = Month(Get(CurrentDate)) and Year(invoiceDate) = Year(Get(CurrentDate));
"this month";
Month(invoiceDate) = Month(Get(CurrentDate) - 7) and Year(invoiceDate) = Year(Get(CurrentDate));
"Last month";
"")
& "" &
Case(
Year(invoiceDate) = Year(Get(CurrentDate));
"this year";
Year(invoiceDate) = (Year(Get(CurrentDate)) - 1);
"Last year";
"Prior to last year")

Now for the scripts.

Make a layout with all the fields you want and call this something like ListView, arrange the body part and footer to show the found set of records in listview.
Sort first your database, by hand, in the order and by field you need, like invoiceDate Descending…or whatever order you want/need.

We have to make first the scripts that will be called by other scripts.

Script: Sort by Date
Enter Browse Mode
Sort Records
Restore; No dialog
Go to Record/request/Page

This script will always sort the records by date in the order you specified in the ‘manual’ sort.

Script: Goto Form View
Enter Browse Mode
Go to Layout (your basic input screen)
Adjust Window
Resize to Fit

Script: Goto list view
Go to Layout (“ListView” (yourTabelName)
Adjust Window
Resize to Fit

Script:Go to Layout after Search
If(
Get(FoundCount = 1)
Perform Script (Goto Form View)
Else
Perform Script (Goto list view)
End If
Refresh Window


Make a script “Find selected dayrange” along these lines:

Set Error Capture (On)
Allow user abort (Off)
Freeze Window
Go to Layout (your layout)
Enter Find Mode
Insert from Index (yourTableName:: DateText_cti)
If (
IsEmpty yourTableName:: DateText_cti)
Enter Browse Mode
Go to layout (original Layout)
Halt script
End if
Perform Find
If(
Get(LastError)
Go to Layout (original Layout)
Else
Perform Script (Go to Layout after Search)
End If

Attach this script to a button. That’s it.

Now for the details and why.

The DateText_cti (cti = calculation, result text, indexed) will calculate the invoicedate value and return a text based upon the ‘today’ date (will change every day). We use this field as ‘daterange’.

The scripts goto list and form are obvious.

Go to Layout after Search will look, after the find, to see how many records are in the found set.
If there is 1 (FoundCount =1) the system will show this record in your standard inputscreen, no use to go to a list view layout for just 1 record and then click a button to go to the final screen/layout.
If there are more than 1 record it will show those records in list view, and then you make your choice to go to a specific record.

With the Find selected dayrange script you will have a list of valid dates and can find the records that match the selected range.
If the DateText_cti is empty, which means ‘no item selected’, the script will bring you back to the original layout and halt.

If there was no error after the find command an other script is called.
If there was an error (Get(LastError) the script will go back to the original layout also.

No way to leave the user in a 0 (zero) record situation.

You can tweak this script to whatever you want, just play around with it.

If you need more info, feel free.
HTH