GetNthRecord ( Addresses; 2 ) & ¶ & GetNthRecord ( Addresses; 4 )
You can get rid of the ¶ character and use a comma "," if you want it on one line.
If you have multiple fields in Addresses such as address, city, state, etc. Then create a calculation field that concatenates those fields and use that in the calc where it refers to 'Addresses'.
P.S. You could also use List ()
Let ( x = List ( Addresses ); GetValue ( x; 2 ) & ¶ & GetValue ( x; 4 ) )
* Edit - reworded
I am (have been for quite a while, unfortunately) trying to do something broadly similar to the topic raised here, and have considered using GetNthRecord, but couldn't really find out whether it would allow me to do what I want to do.
When we take a booking for a cottage in our "Bookings" table, a Booking_ID is auto-generated. This is being used as the testfield in a "not IsEmpty" calculation for the "CottageWeek_Booked" field in the relevant "Cottage" table record, so that that shows "Booked" for the week in question.
So far, so good.
Now, we also take bookings for two or three weeks. The "Bookings" record generated for a booking has a Week_Start_Date field, which is used in the relationship to create the "CottageWeek_Booked" entry in the relevant "Cottage" table record. A booking for 2 weeks gets assigned "2 weeks" in its "Period" field. At that point the "CottageWeek_Booked" field in the relevant "Cottage" table record for one week later should also read "Booked". Likewise for 3-week-bookings, again one week further in the calendar.
I have been playing around forever, creating fields and auto (calculate) filling them for week records, such as "Booked_Previous_Week" and "Period_BookedFor_Previous_Week", to then allow lookups or calculations within one record, to autofill the "CottageWeek_Booked" field if needed. Whenever I set up a calculation that would do such a thing, the fields start returning "?". I'm guessing that is because in essence there is then a "circular" calculation being created.
I have also been thinking of using something like "GetNextRecord", or "GetCurrent+1stRecord" and "GetCurrent+2ndRecord" in the Cottage table, but cannot find anything about this anywhere. Is something like this possible, and if so, how?