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!!!

Just put the field from B into a layout in A.

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.

:?

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.

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???

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???

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.

Here is the 2 databases

Thanks Queue for your help

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?

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.

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

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.

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

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.

Great!

I created an "alt. cost" field with a calculation using Case and IsEmpty to filter it.

Works like a charm.

Thanks Queue.