field value from portal row?

[GlennC]

Is there a way to have a field value auto-set from the value of a field in a particular portal row?

I want to do something like this:

Field a = ::field x 1st portal row

Field b = ::field x 2nd portal row

Field c = :field x 3rd portal row.

I need to 'hardwire' this somehow - it re-calculates daily with 000's of records so a script approach using 'set field' or similar is not practical.

[bruceR]

You'll do better by presenting a more complete picture of what you are trying to do.

[GlennC]

What does "You'll do better..." mean? It sounds very condescending.

I'm trying to find out if there is a way to have the value of a field calculated from a field in a portal on the layout, the value depending on the portal row.

For example if I want to get values for 'field a' and 'field b', where 'Related field::c' is a field in a portal on the layout. I want 'field a' to be the value from 'Related field::c' as it appears in the first row of the portal, and 'field b' to be the value from 'Related field::c' as it appears in the second row of the portal.

I'm wanting to know if this can be achieved by hard calculations, ie without scripting.

Any help appreciated.

[Ender]

To have a calc in the parent record look at a specific record in a related file, you need to have a relationship between the parent record and the specific related record. Usually a related record doesn't know what what row in a portal it is used in until it actually happens. But for this relationship, the related records would have to know ahead of time (with a stored value.)

So, either the parent record must store the primary key for each different related record, or the related records must be serialized with 1,2,3,etc, and reserialized for each parent. I think this latter approach would be possible with no scripting, as long as the related records are only children of one parent record.

The related (child) record would need a self-join relationship on the Parent ID, then an auto-entered calc for its sequence number:

Sequence# (number, auto-entered calc) = last(SelfJoin::Sequence#) + 1

Then for the relationships from the parent file to this file for each sequence number, you'll need a calc in each file to make a compound key:

In the child file:

ParentID_Sequence# (calculation, text result) = Parent ID & " " & Sequence#

In the parent file:

ParentID_Sequence1 (calculation, text result) = Parent ID & " 1"

ParentID_Sequence2 (calculation, text result) = Parent ID & " 2"

ParentID_Sequence3 (calculation, text result) = Parent ID & " 3"

Create relationships between each compound key in the parent file and the compound key in the child file.

Note that if it's possible for the user to delete portal rows, then the sequence numbers in the related file would need to be re-serialized with a script after the deletion.

[-Queue-]

Depending on the relationship's sort order, you might also be able to do this using a value list of the related field and unstored calculations of

Middle( ValueListItems( Status(CurrentFileName), "yourValueList" ), Position( "

[GlennC]

Thanks very much for the replies. Ender, yes this is a solution for me as the related records have only one parent. Queue - I've got a bit of digesting to do there. I can't immediately get my head around it but I'm sure the exercise will be useful.

Also I remembered a previous post of mine from many moons ago asking about a similar issue (makes me think I'm going round in circles). Bob Weaver created a solution using (status(currentrecordnumber)= 1*Related field. As an unstored calc the status part will equate to true(1) if it's row 1 of a portal and false(0) if not. As such only the field in row 1 will have a value. Another calc field in the parent such as sum(Related field) yeilds the required value. Quite a neat solution.

Thank-you again.

[wmugrad28]

Hello Ender,

I was looking for an answer to a pretty similar question. I have a field on a layout that I would like to grab a value (it's actually a date) from the first portal record (I know I want the first portal record since it is sorted.) I am having trouble getting it to work. Do you have an example you could post... or could you explain to me how a number gets into Sequence# in the first place?

Greg

[-Queue-]

Is the portal sorted differently than the relationship? If not, then relationship::field refers to the first related record's field according to the relationship's sort order. If it is, then

Commit Records/Requests

Go to Field [one in the portal]

Set Field [somefield; relationship::field]

should work.

[wmugrad28]

Queue,

I was trying to do this without a script like the first post.

Greg

[Ender]

If you only need the first related value, then simply place the related field on your layout. The first related record is what will show.

[wmugrad28]

Thank you guys for your quick replies.

The problem with that solution is that it goes to the FIRST related record it comes to in that table. The records in that table are in no particular order; however, they are shown in the order I want in the portal since it has been sorted by that field.

What I have is a table Employee (which lists all company employees) and a table Employment (each record tracks when an employee was hired and terminated... we have a few employees that have come back after leaving). My portal on Employee shows all related records from Employment sorted with the most current hire date first. I want to use that "Hire_Date" field in the first row of a portal in a calculated field that sits in the Employee table.

Greg

[Ender]

Set the sort order of your Employment side of the Employee<=>Employment relationship to sort by Hire_Date descending. Then the first related record will be the newest.

[wmugrad28]

That's a great idea, but what happens if an employee is terminated and then rehired. I want to make sure that the field on Employee is updated and not outdated. Once that related data is put into a field, doesn't it just sit there and not change?

Greg

[Ender]

Related fields on a layout change immediately when the data in the related field changes. I'm not sure where you're stuck.

[wmugrad28]

Weird. I don't know what I was getting this confused with.

Thank you for helping me get this to work.

Greg