How do scripts work??

[Budweiser]

I need a bit of advice on how scripts work in Filemaker Pro 7.

I'm trying to build an invoicing system and have lots of automations I'd like to build in, but can't work out how to do it.

My current issue is this:

I want a button that, when clicked, will populate a field with the count of the number of records in the current table that relate to a different table.

The current table is 'Invoices' and it has a relationship with 'Customers'. When the button is clicked, a field should be populated with the number of Invoices that the customer currently has.

I can't work out how to do this. The Scriptmaker tool lets me choose from a finite list of functions, but how do I specify my own scripts and get it do this kind of task?

[T-Square]

Actually, I think you have your tables backwards. To determine how many invoices a client has, you should start with the clients table. Think of it this way: you're trying to find out an aspect of the *client*, so you should build a layout with the client as the base.

If you do that, then you don't need a script to determine the number of invoices a client has; you can create a calculation field in the Clients table that will automatically give you this. Assuming that you've created a relationship from the Clients to Invoices table (let's call the related instance of the Invoices table "CliInv"), you would create an unstored, calculated number field in the Clients table that had as its formula:

Count(CliInv::InvID)

Where InvID is a field in the Invoices table that is always present. Putting that field on your client layout should give you the number of invoices for the client currently displayed on the layout.

If you really DO need to indicate how many invoices the client on the current invoice has, you may still be able to use this mechanism. I would look at setting up a relationship graph where you had the following table occurences:

CurInvoice -> CurClient -> CliInv

Link CurInvoice::ClientID to CurClient::ClientID

Link CurClientID::ClientID to CliInv::ClientID

In other words, you have TWO instances of the Invoices table--one for the current invoice linked to the current client, and one linked "on the other side" of the client table. In essence, the CliInv occurence "contains" (virtually speaking) only those invoices linked to the client listed in CurClient, and the calculation above would still work.

HTH,

David

[Budweiser]

OK, I've gone with your first suggestion and added the following calculation field to Companies:

Count(Invoices::Ref)

Then I've added a field to the Invoices Layout to display this field from Companies. But it shows empty. When I add a new record and select a company from the popup list, it still shows empty.

Any ideas?

Also, doing this as above will mean that every invoice will have the same number, because it would be recalculated every time I view it? How do I get it to store the value at that moment and then never change it for each invoice?

[BobWeaver]

If you are saying that when you look at an invoice, you want to know how many other invoices there are for that same client, all you have to do is create a self-join relationship in the invoices table based on the customer number and then count the self-related records:

Count(selfJoinInvoices::CustomerID)

[Budweiser]

But this value would then change every time I add an invoice. I need each invoice to have a permanent unique value. My invoices have the following format

CustomerRef - Number

E.g. SEL9-005, where this is the fifth invoice for customer ref SEL9.

When I add a new invoice, I want it to determine the next invoice number by looking at how many invoices there have been previously and adding 1. When I next look at the same invoice though, it needs to have the same number, even if other invoices have been added since.

[sbg2]

See if the attached does what you want.

CustRef~Inv.zip