spectrum Posted January 24, 2005 Share Posted January 24, 2005 New at the Forum and getting deeper into FMP6. I have 2 sets of records in 2 databases. database A with 20 records, each record indicates a succession of tasks to do. database B with 5 records. each record provides the cost of a task based on multi-criteria I want to be able to have on database A, for each task, the cost of the task. This cost, would be picked up from the database B using a relationship. I'm stuck with trying to associate the 2 databases. They don;t have the same number of records, so a correspondance by an ID relation doesn't work. Any help would be greatly appreciated!!! Link to comment Share on other sites More sharing options...
transpower Posted January 24, 2005 Share Posted January 24, 2005 Just put the field from B into a layout in A. Link to comment Share on other sites More sharing options...
spectrum Posted January 24, 2005 Author Share Posted January 24, 2005 Tried this, doesn't work. a) created define field on db A with same name as field in db B The data in records on db B doesn't show up in db A. created a relationship... no result c) Tried a portal on db A of db B field with cost. doesn't show up. :? Link to comment Share on other sites More sharing options...
-Queue- Posted January 24, 2005 Share Posted January 24, 2005 You need to relate A to B based on the task identifier, so that when you select a different task id, the related cost field changes. You do not want to relate based on cost, A's unique id, the task name, or other non-unique, irrelevant fields. Link to comment Share on other sites More sharing options...
spectrum Posted January 24, 2005 Author Share Posted January 24, 2005 Still very confused Yes, I though it would be simple and create a filed in dbA with same name as in db B to insert it into the layout. But since A & B don't have the same number of records. there is no peer correspondance. So, i need a field in A that includes a calculation that will select one of the 5 records from db B to apply based on a code filter in db A. Do i need to use a script with a loop to do this or is this can be solve more directly with relationships??? Link to comment Share on other sites More sharing options...
spectrum Posted January 24, 2005 Author Share Posted January 24, 2005 I have a task identifier. it's a code in A that exist in B to define each record. What function can i use to select the proper record and a specific field from the identifier value??? Link to comment Share on other sites More sharing options...
-Queue- Posted January 24, 2005 Share Posted January 24, 2005 That is what I meant by relating based on task id. Assuming each task has a unique id (which it should), create a relationship from the task id field in A, (which is more than likely the field used to select a task) to the unique task id field in B. When you change the id in A, any related fields from B on A's layout will automatically update to show the correct information from B. If you need more help, Stuff and attach your file. Link to comment Share on other sites More sharing options...
spectrum Posted January 24, 2005 Author Share Posted January 24, 2005 Here is the 2 databases Thanks Queue for your help Link to comment Share on other sites More sharing options...
spectrum Posted January 25, 2005 Author Share Posted January 25, 2005 I've attached the 2 databases to of the problem on my previous posting. Working further on this, I found out that one of the problem is using repetitive fields. It would be practical, since i have a series of tasks to list and add. Is there any way it could work with repetitive fields? Link to comment Share on other sites More sharing options...
-Queue- Posted January 25, 2005 Share Posted January 25, 2005 I can't open Stuffed files at work, but someone else may be able to help before I can get home and look at them. Link to comment Share on other sites More sharing options...
spectrum Posted January 25, 2005 Author Share Posted January 25, 2005 I tested them and was able to download and open them. I'm attaching them again with a different compression mode. I've progressed thanks to you. These 2 files have been updated. At this point, I can't use repetitive fields so i have to create separate fields, task_1_code, task_2_code, ... and task_1_rep, task_2_rep, ... and task_1_cost, task_2_cost, ... Is there is a solution with repetitive fields? On top of it. I have to be able to adjust any of the task cost directly on the field. Is there a way to "allow entry into field" combined with a calculation? Download "databases.zip" attached databases.zip Link to comment Share on other sites More sharing options...
-Queue- Posted January 26, 2005 Share Posted January 26, 2005 I could download them but not open them, because I can't get Stuffit installed on this computer for some reason. Thanks for the zip files. What you need is a file for Project_Tasks, a join file where each record is a single task for a project. Relate this new file to the projects one based on Job ID and allow creation of related records in this file. Put all the fields you have set to repeating into this file as non-repeating fields. Then put a portal in your projects file based on this relationship, to display the related fields instead of repeating ones and format them as desired. Then make the total job cost be a calculation of Sum(relationshipToProject_Tasks::Task_Cost) in your projects file. Link to comment Share on other sites More sharing options...
spectrum Posted January 26, 2005 Author Share Posted January 26, 2005 Almost there, I did as you said, Queue, and created a 3rd db, database C that includes single task layout then created the portal on db A . I attached here the zip "three_databases" that reflect the change above. The only remaining problem is that in some occasions, a job as one or more task(s) that requires to adjust the cost directly in the field on db A. Currently, the task_cost is the result of a calculation and doesn't allow for "an entry into field". Is there a way to have both a calculation and allow an entry into field if needed? three_databases.zip Link to comment Share on other sites More sharing options...
-Queue- Posted January 26, 2005 Share Posted January 26, 2005 You could create an 'alternate cost' field and define your calculation to use that field if it is not empty, and otherwise to use the cost * rep result. Link to comment Share on other sites More sharing options...
spectrum Posted January 26, 2005 Author Share Posted January 26, 2005 Great! I created an "alt. cost" field with a calculation using Case and IsEmpty to filter it. Works like a charm. Thanks Queue. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7028 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