Jump to content

Struggling with a Relationship Case


This topic is 6631 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!!! confused.gif

Link to comment
Share on other sites

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.

B) 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

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

Still very confused confused.gif!!!

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

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

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

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


Link to comment
Share on other sites

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

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?


Link to comment
Share on other sites

This topic is 6631 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 account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.