Pulling field contents out of a portal

[newtothegame]

I have a simple portal in one layout of a table that contains two fields: the first is a numeric field containing the Record ID (it must be the Record ID and not the Record Number) of a related record from a self-joined table to the original table, and the second is a text field. I want to pull the contents of the portal's text field out of the first portal row (only) and insert those field contents into another text field in the same record but a different layout of the original table. Is there preferably a calculation, or less preferably a script, that will allow me to do this? GetNthRecord only seems to work with Record Numbers, not IDs and I MUST use the Record ID for this exercise to be sure of pulling up the correct record.

Thanks as always.

[comment]

Can you explain the purpose of this exercise? It sounds like all you want is to duplicate data from one field to another in the same record - which is easy to do, but why would you want this?

[newtothegame]

I need to use text from the original (and/or related) field to set another trigger field (to "y") which will trigger (if and only if "y") a set of calculations for other fields. It will greatly simplify and idiot-proof the progamming for those other fields if I do this. The trigger field will set to "y" only if the text contents of the original field are certain words. The original field is looking back for the latest record, based on time, (not based on the record number as there may have been new records inserted that are intermediate in time between the previous and current record number - or even duplicates which result in the record number duplicating - which mess up the record numbers' order for this purpose).

I have therefore created a self-join relationship based on the related timestamp being less than the current timestamp and the Record IDs (which ARE unique - hence my using them being unequal and I've created a portal based on this relationship with the records sorted by timestamp descending and containing just the Record ID and the related text field. (I tried using the Max function and it not surprisingly didn't work correctly looking for text fields as it does with calculable formats such as number, date, time, timestamp etc).

Just as I wanted, for each record in the current table the record within the portal with the related field containing the data I need is always in the first record of the portal. I just need to take that data out of that related field in the first portal record into a text field in the current record which I can then use to establish the criteria to set off the calculation trigger, or else have a function and syntax which set off the calculation trigger directly from the related field in the first record of the portal.

I hope that makes sense. Let me know if not. Thanks Comment. Knowing you, I'll be surprised if you don't have the answer and, knowing me, I'll be surprised if I'm not kicking myself for not thinking of it when you tell me.

PS: Hopefully, after my first thread, you'll be at least pleased that I'm now using portals and, at times, finding them very handy!!!

[comment]

I am afraid you have lost me right here:

I need to use text from the original (and/or related) field to set another trigger field (to "y") which will trigger (if and only if "y") a set of calculations for other fields.

I suggest you put the technique aside and explain what are you trying to achieve by it. Use names for your tables and fields, so that we can visualize the problem.

[newtothegame]

Sorry, I'll try another explanation but first preface it by saying that my "technique" of using the "y" trigger field is something I've used extensively in the past for various purposes and it will help if I use it for this one. I WILL however stay away from it here as it's not related to my actual problem.

First, to simplify any confusion about tables, everything occurs within the same table, called "Timesheets", (just different layouts within that table). The relationship relevant to this discussion is a self-join within this table which I'll describe below.

I have a series of calculations which are sometimes prevented (intentionally within my programming) from occurring within certain records in this table under certain conditions. (I don't need to complicate the visualisation by describing these calcs or the reasons for their occasional prevention as it's not relevant here).

What is relevant is that I want to add an exception which will additionally and conditionally allow these calculations to proceed, (probably using an "If" statement) with that exceptional condition being if one of the table's text fields (which is called <Shift Name> - as in work shift) - from the record previous to the active record - has the field contents, "Penalty Shift" (and possibly one or two other shift names but that's not an issue as other shift names could be obviously and easily accommodated once I get through the bit that I'm describing here).

The record previous to the active record is determined chronologically, with the self-join relationship that I've created being the Timestamp for the Start Time and Date fields for each record is greater than or equal to the Timestamp for the Start Time and Date of other records and the Record ID is not equal to that of other records (the latter to prevent the active record relating to itself).

I need to use the Record ID to establish uniqueness for each record as there exists the probability that records will have been duplicated or added randomly and Record Numbers can duplicate under these circumstances. This means that using the GetNthRecord command to a related record doesn't work, (the result is an "?"), if I use Get(Record ID) instead of Get(RecordNo) within the GetNthRecord command. I absolutely need the procedure to look at the immediately previous chronological record and the only way I've been able to correctly pull up the result I want is to set up a portal using the relationship described in the previous paragraph. The portal is sorted by descending Start Time and Date Timestamp and contains the <Shift Name> field.

The result that I want, as I'd hoped, is contained within the <Shift Name> field in the first, or top, record within that portal. What I need to do is pull that result out and use it as the condition to satisfy the intention I mentioned earlier. Here is how the correct logic but not the correct syntax would look:

'If (<Shift Name> from the first record within the portal="Penalty Shift"; etc; etc)'

The 'etc;etc' I can deal with; I just haven't been able to come up with a method to make the 'If (<Shift Name> from the first record within the portal="Penalty Shift";' part work.

What I need you to help me with is the syntax/programming command/script steps which will achieve that. Hope that all now makes sense. Thanks again for your concern and help, Comment.

[newtothegame]

Another way to describe what I'm after, since I'm only looking from a result from one related record (which doesn't really require a portal - it's just that the portal was the only way I could find that would display the desired result), is I need to pull out the contents of the field <Shift Name> from the maximum record from the related table to use for my "If" function/condition.

It sounds very simple and obvious - you'd think the Max function would work as it does for other calculations that directly result from numerical data that is calculated as Max - e.g. Max (Time) or Max (Date). But I need to establish the related record with the Max(TimeStamp) first and then get the field contents of the different, text <Shift Name> field from that record.

I still think I'll kick myself when you tell me the answer...

[bruceR]

"This means that using the GetNthRecord command to a related record doesn't work, (the result is an "?")"

No, what this means is that you have not created a valid calculation.

See attached. See tooltips and tooltip calc for field "Project"

Time Billing.fp7.zip

[comment]

Well, I cannot pretend I follow all of that. You could have attached a file instead of describing it - but even that wouldn't make the real purpose any clearer.

I "think" that if you have a TimesheetID field, with auto-entered serial number AND if records are created in chronological order, you can get values from "the immediately previous chronological record" by defining a self-join as:

Timesheets::TimesheetID > Timesheets 2::TimesheetID

and sorting the Timesheets 2 records by TimesheetID, descending.

Once you have done that, a reference to:

Timesheets 2::Shift Name

will return the 'Shift Name' value from "the immediately previous chronological record". No portal is necessary for this - this is all done at the data level.

However, a calculation that refers to the related record cannot be stored, and if you are planning to "trigger" something by changing the related value, you are likely to be disappointed.

[newtothegame]

Thanks Bruce and Comment.

First, Bruce, GetNthRecord DOES work with a related record (as in return a result) if I use the Record Number in the GetNthRecord command (as opposed to Record ID which returns the ?) but it doesn't necessarily give the correct result because the Record Numbers can duplicate if records are duplicated which will happen sometimes in my file.

In fact, Comment's idea of using serial numbers and mine of using Record ID as related fields in a further relationship also didn't work because, although unique, they can return an incorrect result if a record which relates to an earlier chronological time has been added to the file later than the active record.

I figured it out though, by simply inserting a new Timestamp format field into the current table calculated as Max(Timesheets13::Start Time Timestamp) - where Timesheets13 is the relationship I described in my previous post (Timesheets 1-12 used for other irrelevant relationships within this file) and then setting up an intermediate self-join relationship, Timesheets14, where the the new Max(Timesheets13::Start Time Timestamp) field in the current Timesheets table equals the Start Timestamp field in the new Timesheets14 table. I then created a further related table, Timesheets15, where Record ID=Timesheets14::Record ID. I then created the actual field I need, called MaxPrevShiftName which is calculated as Timesheets15::Shift Name. Finally, the trigger field which is calculated If(MaxPrevShiftName="Penalty Shift";"y";"")

Bingo! It works perfectly.

Comment, I also took on board your remark about the non storage of the trigger field but this is just the way I want it - I want it to switch off and cause the dependent calcs to also switch off if the situation in the previous record changes to one that warrants same and back on accordingly, etc, etc. Lookups could have gotten around the problem you mentioned but the trigger won't switch on and off as required in that case so related records are just the way to go in this case.

I think I've worked one out for myself! I must be improving not that that's saying much! Thanks for your input, in any case, people.

[comment]

Well, I am glad you worked it out, because I still don't know what it's about. A few notes, however:

Record Numbers can duplicate if records are duplicated

I am not sure what you mean by "Record Numbers". The value returned by Get (RecordNumber) cannot be duplicated. OTOH, the value depends on the current found set and sort order - so it is not suitable for pointing at a permanent location.

didn't work because ... a record which relates to an earlier chronological time has been added to the file later

That's why I said "if records are created in chronological order". If not, you could define the self-join as:

Timesheets::Timestamp > Timesheets 2::Timestamp

and sort the related records by Timestamp, descending. Then again a reference to:

Timesheets 2::Shift Name

will return the 'Shift Name' value from "the immediately previous chronological record".

[bruceR]

Thanks Bruce and Comment.

First, Bruce, GetNthRecord DOES work with a related record (as in return a result) if I use the Record Number in the GetNthRecord command (as opposed to Record ID which returns the ?) but it doesn't necessarily give the correct result because the Record Numbers can duplicate if records are duplicated which will happen sometimes in my file.

I think I've worked one out for myself! I must be improving not that that's saying much! Thanks for your input, in any case, people.

Worked what out, exactly? You've come to a significant list of invalid conclusions, and I suggest you work this through a little further.

As mentioned by Comment, no the record number cannot be duplicated. You're referring to something else, apparently a field you have created and named record number; or perhaps an auto-enter value which was the record number at time of creation.

[newtothegame]

Ok -sorry the record number can't be duplicated but it DOES return some rather inconsistent results as records are added deleted or duplicated. I was actually getting duplicate results when I did tests via various self join relationships to try to get the correct result. Afte my experience with it I've deliberately avoided it for this and similar exercises looking for chronological ordered previous records. Despite you guys' misgivings, which I appreciate as you're making sure I get it right, my solution works witout records having to be sorted which means it will work as a straight calc without any need for the user to do anything or for me to add a script.

Thanks again. Gotta go out for the day, back later.