Contacts File Advice needed

[Ugo DI LUCA]

I often come across bizarre situation in my retail/wholesale business !

- An employee from my professional customers can buy to my shop as an individual (for its personal consumption).

- An employee from my professional customers (or a single professional customer) can be a "service provider" when he drives a customer to my shop (he is paid a commission on sales for that).

- A customer can be a supplier (I sell some products to another wholesaler, from whom I buy some other products).

This last situation is very annoying as it leads to problem with invoicing, as some wholesalers substract from my invoices the amount of their invoices. That means that I should have the balance due in my customer file relate (sometimes) to my purchase (which isn't logic at all)...

Many of you created a single contacts file, and from the situation described over I thought this could have been the solution. But I didn't came up with anything as this isn't only a "contact problem", but mainly a "hierarchy" problem.

Keeping the Single Contact File, would it be reliable to have it "linked" to a unique file instead of my actual files (customers, suppliers, tax collectors, service providers) ?

Surely the infos needed are different from customers to suppliers (apart from contacts) but apart from different layouts depending on the respective "category" I cannot see the other cons of that structure.

I would appreciate any comments or advice for this. Thanks in advance.

[Kurt Knippel]

Well generally contacts are people and nothing more. The company that they work for might be a "supplier" or a "customer" but not usually the person.

That said, even if we move this whole discussion up to the company or account level, you have the same problem in that some companies are both customers and suppliers.

In this situation, I would suggest the following. You have in your company/account file a unique record for each Company-Status. If you do not have a Company or Account DB, make one. Then you have a Contacts DB, with a record for each person. These two DBs would then be linked by a third file, since some contacts may be related to the "customer", some to the "supplier" company and some to both.

The invoiceing issue is a sticky one, and I am not educated enough in accounting principals to give advise on how to solve that.

[jasonwood]

some wholesalers substract from my invoices the amount of their invoices. That means that I should have the balance due in my customer file relate (sometimes) to my purchase

Wow that is odd!

First of all, I would only have one database for clients/suppliers (accounts)... and one for contacts (for those companies with more than one contact).

Without putting too much thought into it... here's generally how I think I would go about this.

I would keep accounts payable on a po by po basis, and receivables on an invoice by invoice basis.

THE PAYMENTS/RECEIPTS DATABASE:

I would process payments and receipts through a payments/receipts database.

Each payment generally has a cheque number, cheque amount (+ other info) and then a series of line items for the po's you are paying, and the amount applied to each one.

Each receipt generally has a cheque--or other reference number, amount, and then a series of line items for the invoices you are receiving payment for, and the amount applied to each one.

When you process a payment, the script applies the changes to the invoices/po's... this will only work when the payment/receipt balances.

If a company subtracts your invoice amount from the amount you owe them for a po, then you would simply have a line item for the decrease in receivables, and a line item for a decrease in payables (you could have a cheque received/paid to make up the difference).

This leaves a good record for the accountant, who will have to debit payables and credit receivables, and debit/credit cash for the difference.

Sorry I've been so brief, but hopefully it will give you some ideas.

[Ugo DI LUCA]

Many thanks to both of you. I am quite to the end of it. I will post tomorrow the structure db test file I created. Hope this file could help someone else.

I started with this structure :

Account.fp5

Contacts.fp5

Customer.fp5

Supplier.fp5

Contact line item.fp5

These files now allow me to :

- have contacts that belong to several companies (i.e. sales representatives)

- have contacts that have several functions in the same company

- use a unique ID for both customer and supplier for accounting purpose

- hold my old customer and supplier files with their particular records

I even discovered a new facility these file give me when dealing with branch or subsidiaries (a unique account for multiple shops or multiple accounts if needed).

This file is already working far enough for what I can see, except for one thing.

For the moment, as contacts belongs to several companies, I cannot link directly contacts.fp5 to account.fp5. I use the Contact line item.fp5 instead, but this structure is weird as using a filtering portal in Account to show a list of contacts can give me some duplicates at the end...

I thus wondered if I could create a calculated indexed field (called "cAccount") in contacts that could "register" all the accounts numbers the contact is linked to. This field would look like

5452fg453x4 & "P" &

4521df789s8 & "P" &

1235fl856g5...were P stands as "return key"!!!

I hope linking this "cAccount" field with "Account#" would allow me to show the list of

contacts fo a given acount. I saw it somewhere, but I cannot remember how this king of field is setup.

Thanks very much

[Ugo DI LUCA]

Hey guys!!

I've completed it...

I have one account file linked to one operator file

One operator file linked to a contact file

A line item for contacts

A line items for operators.

I can thus have :

One operator be both customer or supplier

One contact in many operator file

Many operator file in one account...

Here is the test file I created. I'm not very proud of field and script definitions. The interface is ugly, but it is a test file.

So I wouldn't post it in the "Sample File".

Lots of portal filtering, highlight, type ahead and the Unique Random Key!

For lurkers only

Thank you both very much for your help.

Please give a look and tell me (if you can) some very bad script structure or big problems you founded.

Contactsfile.zip

[Kurt Knippel]

One comment I would have is that the archive that you posted has many files in it and some of them are more archives. I would suggested cleaning it up and reposting it. Make it a single ZIP file with just the Filemaker files in it, do not zip the individual Filemaker file.

[Ugo DI LUCA]

Sorry for that. Not used to it. Here is the new attacment

CONTACTS.zip

[Kurt Knippel]

Ohh, sooo much better! Thank you.

Looking it over I am a bit confused by the interface and terminology, but then I am not familiar with the specs or the specific lingo of the industry this is intended for so I cannot really complain about that.

I think that you are on the right track, you just need to clean things but and maybe decide on some conventions for naming fields and such. Also look at a way to really make buttons stand out from the rest of the interface, as it is things kind of all blend together.

[Ugo DI LUCA]

As I said first, this is a poor interface as it was only for testing. Working along at night makes me go to quickly into field and script definitions, but I already solve part of that in the definitive interface for my business.

Two other things please :

1. I'm really confused with creating a "go to existant records in related file" script into a portal.

The method I use for the moment is :

a. perform script in related file :

Show All records

Omit records

Show Omited

Omit records

b. perform script in main file

Go to related records

c. perform script in related file

If Status(CurrentFoundConts)>0

Show message "There is already a record for that..."

Sto script

End if

I'm sure there is another way...

2. What do you mean exactly by "make buttons stands out of the interface"

Thank you.

[Kurt Knippel]

1) the scripting I use for going to a related record is generally very simple, 2 lines usually. Goto Related Record, then run a script in the related DB to put me into the proper interface. Then do everything else in that DB.

2) Your interface is very "grey" and so are the buttons. There were buttons all over the place that I recognized from clicking in areas and I also knew from my own development efforts, but users may get confused unless they are made more obvious.