Automatically filling rest of form based on field

[Pinky]

Hey there,

Not sure if this should belong here (or in Define fields). Here it goes.

I've searched the forums for this (but couldn't find an answer). I need to make multiple invoices per day, and it would be nice if FM could automaticaly fill in certain fields (such as Address, Phone, etc...) when it sees that the name I've put in the Name field matches a previously entered name. To do this manually, I'd issue a search on the name, then duplicate the file. This however copies the entire invoice, not just the custumer info. Any way it can be automated ?

Also, any good books on FMpro you would recommend ?

Thanks a lot !

[jasonwood]

You should have a separate clients database, then you enter the client number on the invoice. The rest of the client information fields are "lookup" fields to the client database. They are filled in automatically when you enter the client number.

You can also script the invoicing from the clients database. Locate a client, click "invoice client" and a new invoice is created for that client. To do this you'd simply have to set a global field in invoices to the client number, then run an external script (in invoices) to create a new record and set the client number equal to that global.

[andygaunt]

To transfer the client number to the global field in the invoices database, you will want a constant relationship from contacts to invoices.

This requires a field in each database Zc_Constant which is a calculation field of 1. Then create a relationship between these two fields.

Because a global field is the same for every record in the database, you can transfer information to a global via a constant relationship using Set Field

Set Field [Constant Relationship::The Global Field, "Whatever you want to set it to" ]

[Pinky]

Thanks all !

Actually, I've used what jasonwood suggested, but with 1 database (with a self relationship), and lookup. So now, all the other fields are lookups for the first match of (Current)Name=(existing record)Name. I did it this way sin I only use 1 database.

Great forums btw...thanks to you all.

[jasonwood]

To transfer the client number to the global field in the invoices database, you will want a constant relationship from contacts to invoices.

I keep hearing this but in my projects it doesn't seem to be necessary. I seems as though I could pick random fields for my relationship joins and they will still be able to get through to the global fields. Am I missing something?

[andygaunt]

A constant relationship is used mainly because you do not have to have an existing record in the related database that relates to the current record. You can use it to transfer from any record information to globals through a 1 to 1 relationship.

It keeps things clean and simple, rather than looking for a relationship that has a match.

[jasonwood]

A constant relationship is used mainly because you do not have to have an existing record in the related database that relates to the current record

...what I'm trying to say is that this is not actually true. I just created two simple databases to verify this:

Clients.fp5

clientNumber

clientName

globalClientNumber

SCRIPT: set field (globalClientNumber, clientNumber); run external script in invoices

Invoices.fp5:

clientNumber

clientNameLookup

RELATIONSHIP: clientNumber = Clients::clientNumber

SCRIPT: new record; set field (clientNumber, Clients::globalClientNumber)

Even with no existing records in the invoices db and any number of records in clients db, this works! The script in invoices creates a new record, then successfully gets the value in the global field in clients, even though the relationship is based on the clientNumber, which obviously is blank at this point.

[jasonwood]

...I took this a step further and put a related field (Clients::globalClientNumber) on theinvoices layout.

Even with zero records in invoices, the value of the related global field still shows up!

[Pinky]

Ok... another question...

I've actually switched to using 2 databases. 1 has the client info; client.fp5 (name, address, phone, email...), and the other is used for the invoice; invoice.fp5. Using lookup works well if the client exists in client.fp5. If he doesnt however, the invoice is still fine, but i then need to add the client again to the client database (after having filled more or less the same info in the invoice). To avoid this, I would always have to first insert a new client, then create an invoice.

Would there be any way to automatically create a new client with the info from the invoice if he is not present in the client database (using the comon fields only, leaving the client specific fields empty), and still be able to fill in the invoice automatically for a client already present in the database...

An idea was to add a button in the invoice that has the following (once the client info in the invoice is filled) :

- check if NAME exists in client.fp5.

-if not

-create new record

-fill in available fields

-end if

So...what do you think ?

[Pinky]

Quick update...

I've added the button, and filling in the fields works well. However, I am having a hard time tryin to tell it NOT to add the client if he already exists. What I have done is :

global variable new_client in client.fp5

In invoice.fp5, the add_client script :

-setField["client::new_client","Name"]; (Name is the new client to add)

-PerformScript[sub-scripts,External:"client"]

In client.fp5, the called script is :

-New Record/Request

-SetField["Name","new_client"]

-SetField["Address","invoice::Address"] (since I know have a name that matches the one in the invoice, the name to name relationship works)

-SetField[....other fields...]

The thing is...how can I verify if he already exists...?

I tried a weird way (this follows the SetField command) :

-Perform Find[restore] (find duplicates on Name field)

-Delete Record/Request[No dialog]

-ShowAll Records

It actually works, but if the client is a new one, the find pops a message saying it didn't find anything (can't seem to turn it off).

If it's already there though, it works: the client is not added).

Anyhow...back to thinking !

----------UPDATE-----------

I've found how to check if the client exists.

In invoice.fp5 created a Temp global variable, and changed the the add_client script to :

-SetField["Temp","client::Name"] (if the name is there, it returns the name, otherwise, Temp will be empty).

-If["Temp="""]

-setField["client::new_client","Name"]; (Name is the new client to add)

-PerformScript[sub-scripts,External:"client"]

-End If

Works well.

What do you think ? Any better ways of doing this ?

Thanks !

[andygaunt]

If you add Set Error Capture [on] to the beginning of the script then you would have to capture the error yourself manually.

HTH

[Pinky]

Hehe...you answered as I was editing my post

Found a "better" way of doing it. See above.