Jump to content
labuzzward

One Name, many records, tired of updating

Recommended Posts

labuzzward

I have an existing file set up years ago. I have employee names and their list of clients (25 or more for each employee), and of course all of the important information for each, phone numbers, names, monthly activity etc.

 

Each employee is assigned a route number.

 

As it is now, if a route gets a new employee or a phone number changes, I change all the records that are tied to that employee, by using the replace field command.

 

How can I change employee data, without having to update every record attached to that employee?

 

Would tables work?

How would they work?

Or is it to late now that I have already defined fields?

 

The data base is self contained, there are no other files attached to it.

Share this post


Link to post
Share on other sites
Ender

It sounds like you currently have everything in one table. If you move to a relational structure, it will be much easier to manage changes to data. Ideally, you should only need to change the data in one place (in one record) and be able to have that change reflected automatically throughout the system.

 

We can help you with the basic design, but I think we'll need to know more about how the "rounte numbers" fit into the picture. Is there only one route per employee? Could a route have more than one employee? Also, what is "monthly activity"?

Share this post


Link to post
Share on other sites
labuzzward

There is one route number per employee. Monthly activity is if they have paid that month.

 

I thought tables might help, this is where I am getting lost.

Share this post


Link to post
Share on other sites
comment

It seems clients should belong to a route - not directly to an employee.

Share this post


Link to post
Share on other sites
labuzzward

Yes, clients are directly connected to a route, that may change every now and then, but employees will change more often.

Share this post


Link to post
Share on other sites
Ender
Monthly activity is if they have paid that month.

 

This seems to imply a need to track payments over time (monthly?). Does this sound right? Is there also Invoicing involved? If so, how?

Share this post


Link to post
Share on other sites
labuzzward

Yes, there are monthy payments, but it is not invoiced, we only track who has paid and who hasn't. It is actually a subscription.

Share this post


Link to post
Share on other sites
comment

Well, if clients belong to a route, and a route belongs to an employee, then switching a route to another employee would automatically switch the route's customers as well. So I don't understand the problem.

Share this post


Link to post
Share on other sites
labuzzward

I think where I am getting lost is how to graph the tables. Since this was already set up, so much information is duplicated. Had I started, using tables would have been the way to go. I believe thinking backward to unwind what was done is tripping me up. Do I graph it to say:

Route #

Employee Name

address

phone number

Share this post


Link to post
Share on other sites
comment

I think you should have at least these tables:

 

• Clients

• Routes

• Employees

 

(You probably need one or two more to track the payments, but I am not too clear on your method there.)

 

 

In each table, put the fields that describe the table's entity, for example client's address goes to Clients, and employee's name goes to Employees.

 

In each table define a unique ID field (auto-entered serial number). These fields are used for relationships, so that you can fix a typo in an employee's name without breaking the relationship.

 

You also need a RouteID field in the Clients table, and an EmployeeID field in Routes.

 

 

Your main relationships should be:

 

Employees::EmployeeID = Routes::EmployeeID

 

Routes::RouteID = Clients::RouteID

Share this post


Link to post
Share on other sites
labuzzward

The file I am using has existing data from an older version of FM, mostlikely going back to FM4! When I brought this up from FM6 to FM7 (or 8) it automatically created a table based on the original file (gee, only 150 fields or so). So when I go to define my tables there is a table called "Sept2003". This table contains all of my original fields.

 

Because my layouts are based on those fields, should I create new layouts in order to use the new tables?

 

I should be able to use the old table to relate with the new tables, is this right?

 

OR should I rebuild the database now that I can generate these tables more efficiantly? Then I could import the data from the original fields.

Share this post


Link to post
Share on other sites
Ender

You could do it either way, but you're probably better off rewriting it from scratch. You can then massage and import the data that you need from the old file.

Share this post


Link to post
Share on other sites
labuzzward

Yes, that is what seems best.

 

Thanks everyone for all the help.

 

This is what happens when you suggest to your company that they need a database for what they want to do. I was tossed the box of software and told to run with it. I am not a database developer, but this program has peaked my interest. I amazed at the dynamics.

 

Thanks again.

Share this post


Link to post
Share on other sites
labuzzward

Ok, I have a brand new file.

I have entered 3 tables

Route #

Employee#

Subscribers

 

Route #::RouteID = Subscriber::RouteID

EmployeeID::Employee = Routes::Emplyee

 

Each table has their own list of fields that apply to it.

 

I created a portal for the subscribers in a layout for my Routes.

The portal works great if I enter a new record to my Subscriber layout, or into the portal to my Subscriber from my Route Layout.

 

However, if I import data to my Subscriber Layout, it doesn't appear in the portal.smiley-cry

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

Terms of Use