1 2 Previous Next 18 Replies Latest reply on Jun 24, 2016 7:12 AM by Mike_Mitchell

# Create calculation based on other record criteria in the same table?

Is it possible to create a calculation in a field with the result based on meeting criteria from another record within the same table?

For example, records will fall within a portal:

Fields:      Names           cActual Date

Bench          1/1/16

Chair           1/15/16

Stool            1/16/16

The calculation would be in cActual Date.

This would essentially say:

for Bench record:     if Name = "Bench" ; result 1/1/16

for Chair record        if Name = "Chair" ; result cActual Date of Bench+14 = 1/15/16

for Stool record        if Name = "Stool" ; result cActual Date of Bench +15 = 1/16/16

Criteria within the table is dependent on field contents of another record within the same portal.

Is this possible?  If not, is there a work around?

• ###### 1. Re: Create calculation based on other record criteria in the same table?

In theory, this can be done using an auto-enter calculation and a self-joining relationship. I'm assuming these three records all point back to a single parent record - there's a common foreign key value that associates them? If so, you can do this:

1) Create a second TO of the table where the portal records live.

2) Create a calculation field in that table that always evaluates to "Bench" (or the appropriate key).

3) Join the main TO for this table to the second TO using both the calculation = "Bench" and the foreign key. This will isolate the record for the current parent where the value is "Bench".

4) In the cActual Date field, instead of a calculation field, use a regular date field with an auto-enter calculation. The calculation should look something like this:

Let ( [

trigger = {some field in the local table that will change when you want the calculation to evaluate}

] ;

Case (

Names = "Bench" ; Self ;

Names = "Chair" ; selfJoin::Date + 14 ;

Names = "Stool" ; selfJoin::Date + 15

)

)

This will update whenever the trigger field changes. (Note: You cannot do this as a calculation field, because it won't be editable.)

HTH

Mike

• ###### 2. Re: Create calculation based on other record criteria in the same table?

Is there any significance to the order in which the records in the portal are sorted? For example, do you always want the record that's earliest in chronological order to appear at the top of the portal and be the one on which all the subsequent calculations are based? Or is it as Mike_Mitchell suggests, and you always want to use the "Bench" (or other reliably predictable value) record as the basis for your derived values?

• ###### 3. Re: Create calculation based on other record criteria in the same table?

Richard- there is no significance to order.  I wanted to keep this as simple as possible for the sake of understanding how this would work.  Also, the predictable value will not always be based on the "Bench".   I may have a "Desk" record based on the "Chair" and a "Shelf" based on the "Desk", etc.

• ###### 4. Re: Create calculation based on other record criteria in the same table?

How do you determine what the "base" record value is?

• ###### 5. Re: Create calculation based on other record criteria in the same table?

For sake of this example, the "Bench" will be the base.  The calc field will be base on another field providing that date.  for example OrigDate, which will be an input by the user.  I did not want to confuse the example by adding fields.

• ###### 6. Re: Create calculation based on other record criteria in the same table?

You don't need a second date. Unless you just want it for some workflow reason.

Regardless, you can handle the "who's on first" question a couple of ways:

1) Set up your calculation field on the parent side to be a list of possible values, like this:

Bench

Desk

When you do that, it will automatically match anything on the child side (self-join) that matches any of the values on the parent side. (It's referred to as a "multi-key".)

2) Use a flag field (perhaps a checkbox) that says, "I'm your Daddy." Then use that on the parent side instead of the name.

• ###### 7. Re: Create calculation based on other record criteria in the same table?

Are the calculated date results supposed to be static or dynamic? That is, do you need to determine them only once for any given record in your child table, and thereafter that value will forever be associated with that record? Or will that record possibly have a different date calculated in the future using a different base value? The former situation favors a lookup approach, whereas the latter would lead you to use something else.

• ###### 8. Re: Create calculation based on other record criteria in the same table?

The dependency, for example chair to bench and desk to chair will remain the same.  The dates will change based on the project.

• ###### 9. Re: Create calculation based on other record criteria in the same table?

This suggests an intermediate (AKA bridge, join, link, merge, etc.) table which would feature, in Field A, the primary key of the "home" (parent) record and, in Field B, the primary key of the "dependent" (child) record. Using your examples: A = bench, B = chair, or A = chair, B = desk. Create as many of these as represent valid combinations, and springboard your dates off this intermediate table.

• ###### 10. Re: Create calculation based on other record criteria in the same table?

Richard-

I am a confused by your example.  Is this the relationship you are referencing?

• ###### 11. Re: Create calculation based on other record criteria in the same table?

Isn't the multi key also considered a join table?

• ###### 12. Re: Create calculation based on other record criteria in the same table?

No. It's a different approach to performing a many-to-many join. But a join table requires, well, a table.

• ###### 13. Re: Create calculation based on other record criteria in the same table?

Hi Mike-

Still struggling with this 1.  I think the self join may be the way to go, but not certain.  Any examples you can recommend I see?

• ###### 14. Re: Create calculation based on other record criteria in the same table?

Do you have multiple "bench" records in the table? If so, how do you determine which "Bench" record is the parent for any given "Chair" or "Stool"?

1 2 Previous Next