guy tanguay Posted February 4, 2005 Share Posted February 4, 2005 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 Link to comment Share on other sites More sharing options...
Kent Searight Posted February 4, 2005 Share Posted February 4, 2005 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! Link to comment Share on other sites More sharing options...
guy tanguay Posted February 4, 2005 Author Share Posted February 4, 2005 Thank you for your answer. What do you mean ? Next," create a means of setting" the Project ID field in "Parts"......... Link to comment Share on other sites More sharing options...
Sxeptomaniac Posted February 4, 2005 Share Posted February 4, 2005 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. Link to comment Share on other sites More sharing options...
Kent Searight Posted February 5, 2005 Share Posted February 5, 2005 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 Link to comment Share on other sites More sharing options...
Kent Searight Posted February 5, 2005 Share Posted February 5, 2005 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. Link to comment Share on other sites More sharing options...
guy tanguay Posted February 7, 2005 Author Share Posted February 7, 2005 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 Link to comment Share on other sites More sharing options...
-Queue- Posted February 7, 2005 Share Posted February 7, 2005 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. Link to comment Share on other sites More sharing options...
guy tanguay Posted February 8, 2005 Author Share Posted February 8, 2005 My question is how "Total Amount" in parts will know to wich record to refer for is Rate ? Link to comment Share on other sites More sharing options...
-Queue- Posted February 8, 2005 Share Posted February 8, 2005 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. Link to comment Share on other sites More sharing options...
guy tanguay Posted February 8, 2005 Author Share Posted February 8, 2005 Ok, it is what i understand, but it still does work. thx guy Link to comment Share on other sites More sharing options...
-Queue- Posted February 8, 2005 Share Posted February 8, 2005 It is and it does or it is and it doesn't? Link to comment Share on other sites More sharing options...
guy tanguay Posted February 8, 2005 Author Share Posted February 8, 2005 sorry, it is and it doesn't work. thank you Link to comment Share on other sites More sharing options...
-Queue- Posted February 8, 2005 Share Posted February 8, 2005 Did you delete the blank BR record in Projects? Link to comment Share on other sites More sharing options...
guy tanguay Posted February 9, 2005 Author Share Posted February 9, 2005 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 ! Link to comment Share on other sites More sharing options...
-Queue- Posted February 9, 2005 Share Posted February 9, 2005 No problem. Glad you're on your way! Link to comment Share on other sites More sharing options...
guy tanguay Posted February 9, 2005 Author Share Posted February 9, 2005 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 Link to comment Share on other sites More sharing options...
-Queue- Posted February 9, 2005 Share Posted February 9, 2005 Make sure Total Amount is a calculation and not a number with an auto-enter calculation. Link to comment Share on other sites More sharing options...
guy tanguay Posted February 9, 2005 Author Share Posted February 9, 2005 Thats it, your the men. thx again for your greatly appreciated help. guy Link to comment Share on other sites More sharing options...
guy tanguay Posted February 14, 2005 Author Share Posted February 14, 2005 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 Link to comment Share on other sites More sharing options...
-Queue- Posted February 14, 2005 Share Posted February 14, 2005 Try using a script with the Refresh Window [Flush cached join results] step. Link to comment Share on other sites More sharing options...
guy tanguay Posted February 14, 2005 Author Share Posted February 14, 2005 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7011 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now