little help re: relationships

[brantritter]

I'm trying to design a personal database for contact and invoicing.

 

On my contact page I have a create invoice button that takes you to an invoice layout. i want to be able to have the contact info from the current record automatically entered onto this new invoice. I can't seem to get it to work.

 

It would also be nice if I can enter the info on a new invoice and have a new contact record created.

 

Lastly, is it possible to create a report showing only records that are labeled from a pull down menu. I have a pull down menu describing the type of contact, IE client, VIP, personal etc.

 

Thank you

Brant

[LaRetta]

Hi Brant,

i want to be able to have the contact info from the current record automatically entered onto this new invoice.

The button on your Contact layout should be something like this (modify as needed):

 

Set Variable [ $ContactID ; Contacts::ContactID ]

Freeze Window

Go To Layout [ layout based upon your Invoice table ]

New Record/Request

Set Field [ ContactID ; $ContactID ]

Commit Records/Requests

It would also be nice if I can enter the info on a new invoice and have a new contact record created.

Since, when in your Invoice table you are on the 'many' side, you can use your relationship to create a new Contact. In your graph dialog (double-click connection between Contacts and Invoices), check 'Allow Creation of Related' on the CONTACTS side (bottom of dialog). Place your Contact fields directly on the Invoice (anything EXCEPT the ContactID). Typing in any Contact field will create a new Contact. But be careful with this one ... it is far too easy for Users to create a new Contact instead of selecting an existing one.

... is it possible to create a report showing only records that are labeled from a pull down menu.

We need more information. The answer is yes ... but the process would depend upon what you are doing. Example: You can script a Find for * which will find any records with a value in this field. You can then switch to a columnar list report (with grouped data) and even count them. By using this field as the CATEGORY (and being sure to SORT by this field), FM can produce a report that (when viewed in Preview Mode) will show a breakdown grouped by this pull down field.

 

LaRetta smiley-smile

[brantritter]

so my script looks like this

 

set variable [$ContactID; Value:Contact Information::ContactID]

freeze window

go to layout ["Invoice_business" (Invoice)]

New record

set field [invoice::ContactID]

commit record

 

however it doesn't want to work

[LaRetta]

set variable [$ContactID; Value:Contact Information::ContactID]

freeze window

go to layout ["Invoice_business" (Invoice)]

New record

set field [invoice::ContactID]

 

Set Field [ Invoice::ContactID ; $ContactID ]

commit record

 

You've given me nothing to go on. "...it doesn't work" tells me squat. So if this isn't why it breaks please be a BIT more specific. smiley-laughing

[polydore]

...

Since, when in your Invoice table you are on the 'many' side, you can use your relationship to create a new Contact. In your graph dialog (double-click connection between Contacts and Invoices), check 'Allow Creation of Related' on the CONTACTS side (bottom of dialog). Place your Contact fields directly on the Invoice (anything EXCEPT the ContactID). Typing in any Contact field will create a new Contact. But be careful with this one ... it is far too easy for Users to create a new Contact instead of selecting an existing one.

...

 

LaRetta smiley-smile

 

One way of minimising the risk is to use a drop-down list based on the name field in your contacts table. Users can select a name (relationship match) from the list (and if the other fields (address etc) are created as auto-enter lookup text fields based on the name match, data will be automatically entered on creation of the related record.

If you place fields from your contact table directly on the invoice, they may change if the contact's details change. Invoices are documents of historical record. Their data should always reflect the data that was contemporary at the time of creation. Better done with lookup fields.

 

Have attached a rough file that illustrates

 

polydore

[LaRetta]

Hi polydore,

 

Invoices are documents of historical record. Their data should always reflect the data that was contemporary at the time of creation.

Usually. I know several businesses that prefer using hard-copy as their historical record. Their database then requires no such redundancy. I prefer the redunancy in this instance and skip the hard-copies. But not ALL contact information even needs to be in an Invoice but yet may be important to capture when creating a new Contact. The question wasn't about filling the Invoice with Contact data (which can be simple Auto-Enter or Lookup), only about creating a new Contact. And there is nothing wrong with 'reverse creation' but you are correct that this must be considered.

 

I have a few concerns here: 1) You are basing the match on name. If you have two Contacts named Bill Smith, you will never be able to invoice the second (existing) Bill Smith because they will not show in the popup. 2) If User selects an existing Contact but changes their mind and begins to type the NEW name, they will overwrite the existing Contact record AND the ID will remain the OLD Contact's ID and 3) You are using 'Allow Creation' but then still using script to set the ContactID in the Invoice. Users may NOT hit this button. In which case, the ID will remain based upon the prior selection's ID.

 

Overall, I didn't go into all of these issues because there are so many approaches and considerations so I'm glad you brought them up. I would prefer to present User with portal of all Contacts with their address/phone (BEFORE User sees the Invoice) in which to select (can be filtered using global EDIT box with auto-complete) to filter portal. Selection of one inserts ContactID. If the Contact is not there, button with NEW CONTACT creates blank Invoice, then typing in the Contact name will (naturally) create a new contact. Options abound ...

 

LaRetta smiley-smile

[polydore]

Hello LaRetta,

Thanks for your reply.

 

1) You are basing the match on name. If you have two Contacts named Bill Smith, you will never be able to invoice the second (existing) Bill Smith because they will not show in the popup. 2) If User selects an existing Contact but changes their mind and begins to type the NEW name, they will overwrite the existing Contact record AND the ID will remain the OLD Contact's ID and 3) You are using 'Allow Creation' but then still using script to set the ContactID in the Invoice. Users may NOT hit this button. In which case, the ID will remain based upon the prior selection's ID.

 

1) - Oops! I'd forgotten (or disregarded; I know this) the issue regarding duplicates not showing in pop-ups.

2) -The data in the contact file for the originally selected customer is untouched, no matter what you do here. But you are right that it could become messy.

(Because I've really only created databases for my own use, I've been aware of how to fix or recover the mistakes. Preparing a solution or sample for someone else requires a more disciplined approach than I'm used to. I will pull my head in a bit!)

3) - To update the ChargeToID field. How is this done otherwise?

 

The filtered portal approach sounds good, but I don't know how to go about it. Is there a sample file somewhere that demonstrates how this is done?

 

Thanks & regards

 

polydore