How to create a Line Itme File

[elfin2771]

I have been working with FM for some time now but I still feel like a beginer. I would like to know how to create a Line Item File.

I live in Perth Western Australia and as much as I love it here, when it comes to FM there is nothing and nowhere to go for help.

I have a Client File for personal information, and Policy File with all the policy information. The information from the two Files go to the Letters File, there I generate a report for the client with updates every 10 days untill the policy is accepted. All this works well. But now I need to create a Line Item File, so that I can keep trak of all the letters sent,but Im not sure how to do this.

Can someone help?

elfin

[Vaughan]

Line Items files are Lawyers -- you only get one if you need one. How do you know if you need a Line Items file? Do a data analysis.

You have a Client file, a Policy file and a Letters file. Ask this question: Can xxx have more than one yyy, and can yyy have more than one xxx. You are looking for a "many to many" relationship which requires a Line Items file.

In your case: if you want to relate Clients and Letters, you only need a line items file if: a client can have more than one letter AND a letter can be sent to more than one client. If a letter is only sent to one client (that is, a letter is addressed to one person only at a time) then no line items file is needed.

Examples that DO need Line Items:

Invoicing systems, where many clients can purchase many products

Enrolment systems, where many people can do many courses

[elfin2771]

Hi Vaughan

Thanks for your reply.

Yes, my Letter File is very much like an invoicing system, the client can have many policies with many insurers,

e.g.

"...your application to....has.....policy...."

"...your applications to....have.....policies...."

"Your 'policies are' yet to be issued by the AC&L and MLC Insurance 'Companies' because of the following unattended requuirement/s."

"Your 'policy is' yet to be issued by the MLC Insurance 'Company' because of the following unattended requuirement/s."

There are then several requirement choices all of which change with each letter that is sent out every 10 days

And then there is the Summary sheet that gives the details of all the different policies for that one client. And then must be updated each year.

elfin

[Vaughan]

OK Line Items files are actually quite easy to make. Just define 2 fields, one for each of the keyfields in each related database. Just match the data types, they shouldn't be have any auto-entered options set.

Then relate the policies database to the line items, and the line items to the letters (or the reverse depending upon which way you work) by "joining" the match fields.

Thw hardest part is fitting the Line Items file into an existing database: splitting and massaging the data in existing records into the new structure is a major task -- often the most expensive part of the operation! It's at this stage that I'd suggest getting professional help if you think you cannot do it yourself.

[Garry Claridge]

Elfin,

In your Client file have a 'client_id' (i.e. a unique identifer). In your Policies file also have a 'client_id' field (these fields do not have to be the same name).

In 'Define Relationships', for the Clients file, create a relationship to the Policies file with 'client_id' to 'client_id'.

Now you can create a Portal in a layout of the Client file. Add fields from the Relationship to the portal and you now have your list of policies per client.

The same setup can be made for a relationship to the Letters file.

Hope this helps.

Garry

[ November 09, 2001: Message edited by: garrycl ]

[elfin2771]

Thanks I'll give it a try

elfin