setting up a db 2

[IRETH]

I am new to Filemaker (I usually use Excel), but have a project my boss thinks needs more than Excel offers. However, I am having trouble with the relationships part of Filemaker.

I am setting up a db that will track itinerant services to students. There will be a number of different districts purchasing services for a number of different students. A number of consultants will be hired to provide the services and I will need to have them sign in and out, and track the # of hours they spend providing the services to the students. I will need to generate 2 reports from this information. First, I need to create a timesheet report for each consultant which will track their sign in and sign out time, name of student served, service provided and a cost for the service provided. I will also need to create a monthly invoice for the district to indicate services received for each student by date and cost and a total bill.

I have set up Tables with District information, Service information, student information, staff info and Time info. I am having trouble relating them so I can get out the information I need. I can do all this in Excel, but the boss seems to think we need a relational database...so I'm struggling with designing my first database. Any help or suggestions you can provide would be greatly appreciated.

 

[DHart01]

IRETH -

In order to link (create a relation) between tables, each needs to have a field that contains the identical information that is in the other table. For example, you could have a field named "employee id number" in one table and a field named just "id number" in the other table. If a record in one table has the identical information in the other table, you can link those fields and be able to access all of the information from both tables.

Likewise, for a third table you would need to have a field with identical information contained in one of the fields in either of the two original tables. The linking fields do not have to be the same in all tables.

I have a database that links part numbers between two tables. One of the tables contains the vendor number of the supplier. A third table also contains the vendor number, so I can link the third table. And so on. Hope this helps.

 

[JeanW]

On the Install CD are several sample files.
One of them is a time Billing file.

I think this can be a starting point for you to keep track of the spending hours and to generate the reports you need.

You will also need a LineItem file where you can combine all the several items to generate the needed invoice.

I think there is a sample file for invoicing as well.

I don-t have those files installed on my system, but they are in the file list on the CD.
Take a look there first and break those files apart to see how things are set up.

From my point of vue, your boss is right, a database will give you more flexibility, but as a starter in database, this is a BIG assignment....

 

[IRETH]

Thanks....yes, I know big assignment. I thought I was on the right track, set things up as you described. Bought two books when I got stuck, and signed up access for an online training, thinking that when I got stuck I could find what I needed online.

I set up the tables, using district ID # each data base as a common link. But where I am stuck is that when I try to make a one to many link on some fields, I am only able to make a many to many link. There must be a way to set the type of link you want one to one, one to many or many to many but, as yet, I can't figure out how to do that. When I link the fields, the link is always many to many.

Interestingly, in both books, and the instructions online I have accessed, this process only garners a couple of paragraphs. I don't understand why I am having such a difficult time with it.

Jan

 

[JeanW]

If I were you I wouldn't care too much about the one to many or the many to many like it shows in the relationship diagram.
This tool is not a real ERD. It is just a graphical representation of links.
You will see that most of the relationships are many to many.

Note the crow’s-foot indicators in the graph. These indicators depict relationship cardinality. The cardinality of a relationship simply answers the question “how many X relate to how many Y?” In other words, cardinality tells us whether a relationship is one to one, one to many, many to one, or many to many.

FileMaker supplies this based on an educated guess.

If a field is defined using the serial number auto-entry option, has “unique” validation settings, or is a “global” field, then FileMaker assumes that the field represents a “one” when it participates in a relationship. Otherwise it assumes the field counts as a “many”.

It’s important to realize that these indicators in the Relationships Graph have no practical significance for your FileMaker system. They do not create or enforce any particular rules about your data.

Only one thing to watch out for, in general, a one to one relationship indicates that the two tables being related really shouldn’t be separate tables at all.
If you have that, there is a design flaw and you should rethink your process.

And for a many to many you need an in between table...

HTH