I'm new to Filemaker but am actually not to far from getting a DB up
and running the way I want it. I can work on cosmetics later but the
table relationships are where I'm stuck.

OK...I have...

A table for CLIENTS
Table for Projects
One to many relationship created between clients and projects,
everything working well.

Now....I also have TALENT. I believe I need an "join" table between
Projects and Talent to be able to see what projects a given Talent
(musician) was on, and what musicians on a given project. All well and
good. Here's where it I'm stumped.

I also want to track EXPENSES.

SOME expenses are Project related, and usually fall into the category
of what I paid a TALENT member on a given project.

OTHER expenses are not PROJECT related, such as a new guitar or new
computer.

In the end, I want to be able to:

1 View a Project and see what musicians were on it and what they were
paid.
2 Create a report at year's end to see how much I paid a given TALENT
member
3 Create a report that shows totals for all expenses by category (the
non-existent yet "Expenses" file would have categories for each
expense ....i.e. "software," "talent," etc.

My eyes are glazing over trying to figure out how this would be set
up, any help greatly appreciated.

Thanks
Tom

Tom

I'm not going to churn out this again, but have a few usefull links you
might want to visit on relations

http://help.filemaker.com/app/answer...-file-database

http://help.filemaker.com/app/answer...part-2-of-3%29

http://help.filemaker.com/app/answer...part-3-of-3%29

Also have a look at:
http://www.vtc.com/products/FileMake...-Tutorials.htm
With many links with short video's about all kind of filemaker related
questions, including relationships

distinguishing between Project and Talent can be solved on many ways,
depending on your needs. Ranging form a simple radio button
[Project/Talent], via a one-to-may (none of the expenses are recurring), all
the way through two seperate many-to-may. [Project-Join-Expenses] [Not
Project-join-Expenses]

Building reports seems an answer for later, as this also depends on detailed
needs and set-ups. It would seems a good place starting with the
relationships first.

Hou je goed / keep well,

Ursus


"aerovons" schreef in bericht
news:b26e77ed-a8b2-446d-ab49-d5a92618cee0 (AT) w36g2000vbi (DOT) googlegroups.com...

I'm new to Filemaker but am actually not to far from getting a DB up
and running the way I want it. I can work on cosmetics later but the
table relationships are where I'm stuck.

OK...I have...

A table for CLIENTS
Table for Projects
One to many relationship created between clients and projects,
everything working well.

Now....I also have TALENT. I believe I need an "join" table between
Projects and Talent to be able to see what projects a given Talent
(musician) was on, and what musicians on a given project. All well and
good. Here's where it I'm stumped.

I also want to track EXPENSES.

SOME expenses are Project related, and usually fall into the category
of what I paid a TALENT member on a given project.

OTHER expenses are not PROJECT related, such as a new guitar or new
computer.

In the end, I want to be able to:

1 View a Project and see what musicians were on it and what they were
paid.
2 Create a report at year's end to see how much I paid a given TALENT
member
3 Create a report that shows totals for all expenses by category (the
non-existent yet "Expenses" file would have categories for each
expense ....i.e. "software," "talent," etc.

My eyes are glazing over trying to figure out how this would be set
up, any help greatly appreciated.

Thanks
Tom

On Feb 7, 5:33*am, <Ursus> wrote:
Thanks for the links, I've been through many of these and for some
reason this particular problem is still puzzling me even after
watching many of these types of videos.

I do understand that a many to many relationship via a JOIN file is
the answer for relating Projects to Talent,
it's the Expenses file and where/how it fits in that is confusing to
me. None are recurring expenses, though many of the same talent is
used on many projects....the amounts paid change from job to job.

Your remark about two separate many to many relationship files seems
like it might be the way to go if I can figure that one out someway.
Thanks so much, I'll continue experimenting.

Tom

Thanks for the links, I've been through many of these and for some
reason this particular problem is still puzzling me even after
watching many of these types of videos.

I do understand that a many to many relationship via a JOIN file is
the answer for relating Projects to Talent,
it's the Expenses file and where/how it fits in that is confusing to
me. None are recurring expenses, though many of the same talent is
used on many projects....the amounts paid change from job to job.

Your remark about two separate many to many relationship files seems
like it might be the way to go if I can figure that one out someway.
Thanks so much, I'll continue experimenting.

Tom

-----

Rough layout

join table and expenses table must suffice. a project may have many expenses
and an expense may be used in many projects. The expense table has a couple
of fields.
ExpenseID (auto fill ID)
descrption
amount
project/non project

Then the join table

Then the TO's project>join>expenses

and ofcourse a script of some sort to create the joined record
When the join is created project/non project could be entered (this could be
done with the same script)
Afterwards it would be easy enough to filter the values for project based on
the expense being project/non project
Even if you wanted you could show project/non project side by side in two
portals
Summarizing or totalling the cost per project could take a couple of
intermediate calculations, but would seem no problem.

Keep well, Ursus

On Feb 7, 2:47*pm, <Ursus> wrote:
and perhaps a drop down for "Category" (hardware, talent, software,
etc)

OK remember I'm new to all this, but so far so good except for
"project/non project"....what is that, a checkbox or value list?

Join table has a field called expensename linked to Expense ID, right?


What is TO? Did you mean "talent" from the TALENT file?

All of the following is pretty advanced for me I fear.

How much would I expect to pay to have this set up by someone? Just
the structure, not the looks or the reports or any of that. Just
structure?

Thanks
Tom

"aerovons" schreef in bericht
news:df78dbce-25a3-47f0-9df7-c25bd3a9db2e (AT) w6g2000vbo (DOT) googlegroups.com...

On Feb 7, 2:47 pm, <Ursus> wrote:
and perhaps a drop down for "Category" (hardware, talent, software,
etc)

OK remember I'm new to all this, but so far so good except for
"project/non project"....what is that, a checkbox or value list?

Join table has a field called expensename linked to Expense ID, right?


What is TO? Did you mean "talent" from the TALENT file?

All of the following is pretty advanced for me I fear.

How much would I expect to pay to have this set up by someone? Just
the structure, not the looks or the reports or any of that. Just
structure?

Thanks
Tom

Tom,
I didn't realise you are this new to filemaker. You managed a relationship,
but don't know what a checkbox is.
Checkboxes, dropdowns, valuelists are all pretty basic stuff to know and
understand.
Seems you have your work cut out. If you are willing to learn, buy a good
book and start at the bottom.
I doubt if you would find someone willing to build a skeleton file, but you
will have to look in your area.
Always try to find someone local (or as local as possible). Try google
first.
Normally prices will be fixed per hour, not per project.

Hou je goed / keep well,

Ursus

On Feb 8, 3:59*am, <Ursus> wrote:
Thanks--- no I know what a checkbox is, and I have made value lists. I
am getting a lot out of some tutorials you linked me to so it's
becoming clearer. I think the main thing I have a question about is
this.

If you have a PROJECTS file, and a "MUSICIANS" files (many to many),
is the JOIN file where you would put the Expenses? So "Expense_ID" key
to foreign key "Expense" (In Musicians) ? If that's he case I think
the problem is solved...

Thanks
Tom

Thanks--- no I know what a checkbox is, and I have made value lists. I
am getting a lot out of some tutorials you linked me to so it's
becoming clearer. I think the main thing I have a question about is
this.

If you have a PROJECTS file, and a "MUSICIANS" files (many to many),
is the JOIN file where you would put the Expenses? So "Expense_ID" key
to foreign key "Expense" (In Musicians) ? If that's he case I think
the problem is solved...

Thanks
Tom

---

Lets see. You don't have files (hopefully) but tables (although files might
work of you wanted)
Just some quick thoughts. There are more ways to solve this. And the next
solution isn't the most elegant.
Expenses would be a comodity. So they deserve their own table.
An hour of dishwashing would cost a steady rate, so you would have a table
Expenses, with a lot of records that hold the prices.
These prices could change, so you don't really want a relationship at all.
Because when you update a price this change would be reflected through all
your previously made records.
Since each project only has one link to possible prices you now need a new
table for a one to many relationship.
you'll have Projects, Prices, Musicians and one join between projects and
prices.
The prices could be put in a value list.
When you create a new record for the join. the price you need could be
pulled from your value list and put in a Cost field within the join. It
needs to be fysically there. So it doesn't change when new rates are
implemented.
All this could easily be done with one script
simplyfied it could look like this:
get ProjectsID
get MusiciansID
get Price
goto join
new record
put projectsID
put MusiciansID
put Price

Like I said this wouldn't win a beauty contest, but it will work, but you
might want to reconsidder your options later in life when you understand
FileMaker more.

By the way..... a TO is a table occurrence, the rectangles you draw in the
relationship graph.
A table may have many occurences
And a file may have many (hundreds) of TO's depending on your needs.

Keep well, Ursus