Calculations From a Newbie

[guy tanguay]

Hi,

I am creating a Database that help me doing the bidding of projects that i could work on. (if i get the job!)

I have 2 Tables : One being about the "project" (each record is data about a specific project)

the second table is about all the different "parts" that need to be acheive in a "project".

In the "Project settings" i have 4 different Rates : Rate A, Rate B, Rate C, Rate D. Each of those rate are for a entire day of work. So, i put a number for each of those Rate: 100$(RateA) , 200$(RateB), 300$(rateC) 500$(rateD). I could change later those -Rate- if i need to.

After that, i would like to go on the "Parts" table and just puch a "Day number" for each "rate" as i do my evaluation bidding: see example=

So, the amount i will have to charge you for me to acheive that "Part" is: "8X RateA", "0X RateB", "2X RateC", "1X RateD"

And, as you can predict my Calculation field call "Part Total amount" will be someting like:

= (#day*RateA)+(#day*RateB)+(#day*RateC)+((#day*RateD)

but it didn't allow me to make that calculation.

Well, i know that the calculation need to know from wich record i am referencing to in the "project" Database wich is someting that i don't know how to do.

But in "project" i have a field call "Project ID" which is a 2 Letter id: ex: BR

So, the 2 ultimatte questions are:

The 1st one:

How can I make, some of the records of the tables "parts" taking their referencing out of a specific record of the table "Project" using the "ProjectID" that i have already set in one of it field

The 2nd question:

Then, How can i make my Calculation field call "Part Total amount" work !

Thanks in advance for Helping me, I will fedex you a beer in the bar of your choice !

I think that will make me take a big step in to my FMP training

guy

[Kent Searight]

If I understand you correctly, you need to have a field in the table "Parts" that is related to the Project ID field in the table "Projects".

So first create a field called Project ID in the table "Parts".

Next, create a relationship between these two fields.

Next, create a means of setting the Project ID field in "Parts" to the same value as the Project ID field in "Projects" every time a new record is created in "Parts".

I'm not really getting the part about punching "Day Numbers" into the "Parts" table but hopefully if you create a relationship, all else will make sense to you.

One more thing. I'm assuming that the relationship you need to establish is a simple one-to-many relationship, i.e. 1 project has many parts. If that's not the case then you'll need to create a join table.

Good luck!

[guy tanguay]

Thank you for your answer.

What do you mean ?

Next," create a means of setting" the Project ID field in "Parts".........

[Sxeptomaniac]

regarding the 2nd question.

You might want to give the specific error message you are getting with your "Part Total Amount" calculation that prevents you from doing that. This specific version you posted would give you an error message because there is one too many parenthesis:

= (#day*RateA)+(#day*RateB)+(#day*RateC)+((#day*RateD)

A corrected version would be:

= (#day*RateA)+(#day*RateB)+(#day*RateC)+(#day*RateD)

But I don't know if that was the specific problem.

[Kent Searight]

Please see the attachment. It's very general and may not be exactly what you want but I think it will help you to understand a couple of the basics of a relational database.

TagID.zip

[Kent Searight]

One more thing I forgot to mention in my posting with the attachment. Personally, I would totally avoid the use of an ID that's manually entered to be used as a key in relationships. I would opt for an auto-entered value in the table "Projects" instead.

[guy tanguay]

Thanks Kent for your example !

Please, see the attachment, wich is a more specific example of what i am trying to build.

So then, you could undertsand better which way of programming will serve me better (Formula,portal,etc).

Thanks again for your time.

SetIDupdate_byGuy_7feb05.zip

[-Queue-]

Make your Total Amount in Parts a calculation of

# of Apple Box * Project::Apple Rate + # of Banana Box * Project::Banana Rate + # of Raisins Box * Project::Raisins + # of Strawberry Box * Project::Strawberry Rate

Then, delete the blank BR record in Projects. You already have a filled-in BR record and the blank one is keeping the related Parts records from pulling its information, since the blank one was created first and so is referred to when calling each of the Project fields.

You could split this file up into additional tables. But I think that may only make things more confusing for your purposes.

[guy tanguay]

My question is how "Total Amount" in parts will know to wich record to refer for is Rate ?

[-Queue-]

The Project table is related to the Parts table based on Project ID. So Project::fieldX from Parts refers to the record in Projects with a matching Project ID, i.e. the related Project record.

[guy tanguay]

Ok, it is what i understand, but it still does work.

thx

guy

[-Queue-]

It is and it does or it is and it doesn't?

[guy tanguay]

sorry,

it is and it doesn't work.

thank you

[-Queue-]

Did you delete the blank BR record in Projects?

[guy tanguay]

Yes i did, but by double-checking it i figured-out where was my mistake.

Now Everythings seem to work the way i wanted and the way you guys did describe it, to me.

I'm sorry to have make you loose some time at the end.

THANKS a lot for your help and your patience to both of you. -Queue- & Kent_S

guy

happy to be able continuing is database !

[-Queue-]

No problem. Glad you're on your way!

[guy tanguay]

Just another question here.

When i go back on the "project" DB et enter different rates for each product. It does'nt update automatically the "Total Amount" in parts until a re-enter the number of product i want for each category. EX: 1 apple, 2 banana, 3 raison, 1 strawberry. Is there a way to make that dynamic ?

thx

guy

[-Queue-]

Make sure Total Amount is a calculation and not a number with an auto-enter calculation.

[guy tanguay]

Thats it, your the men.

thx again for your greatly appreciated help.

guy

[guy tanguay]

Ok, Another calculation Update problem...

Now, i have put a Summery field in the "parts" Database. But i use it only in a "project" Layout in the same view of where i can change my rates. The idea is when i puch a new rate i wanted to see the total automatically updated for all "parts" record attach with the same project ID.

It work, but i do have to switch back and forward with another record in "project" database in order to see the sum updated.

thx

guy

[-Queue-]

Try using a script with the Refresh Window [Flush cached join results] step.

[guy tanguay]

Hi, thx for your answer.

That work, but i have to run the script every time that i want to see the current result ? Or there a way to apply it a so it will run automatically as i puch new rate ?

thx

guy