Deriving a value from another record

[birder]

I'm trying to build a global calculation that returns the value of a field in another record.

I need the field value ("field1") for the record where the field ("startDate") matches the start date of the current week.

I can build a calc that determines whether the current week's start date (7/16/2017) matches "startDate" for a given record, but I need to show the value of "field1" for that record only.

I tried creating a self-join relationship to isolate a related record based on "startDate", but any such date calculations are unstored and thus not usable as match fields in a relationship.  I'm probably missing something obvious, but for now, can't figure this one out.

Edit:  I'm currently using FM12 Advanced.

Edited July 19, 2017 by birder

[comment]

I am mostly guessing here, but: 

If you define an unstored (not global) calculation field that returns the start date of the current week, and use it as a matchfield in a self-join relationship, so that:

YourTable::cCurrentWeekStart = YourTable 2::StartDate

and place the Field1 field from YourTable 2 on the layout, it will show data from the first record whose StartDate matches the current week's start date.

 

32 minutes ago, birder said:

any such date calculations are unstored and thus not usable as match fields in a relationship

Unstored calculations can be used as matchfields on "this" side of the relationship (i.e. the side that requests the related records, not the one that supplies them).

 

[siroos12]

28 minutes ago, comment said:

I am mostly guessing here, but: 

If you define an unstored (not global) calculation field that returns the start date of the current week, and use it as a matchfield in a self-join relationship, so that:

YourTable::cCurrentWeekStart = YourTable 2::StartDate

and place the Field1 field from YourTable 2 on the layout, it will show data from the first record whose StartDate matches the current week's start date.

 

Unstored calculations can be used as matchfields on "this" side of the relationship (i.e. the side that requests the related records, not the one that supplies them).

 

Hi comment;

 

Is there any way of doing this using "ExecuteSQL "?

[comment]

19 minutes ago, siroos12 said:

Is there any way of doing this using "ExecuteSQL "?

I don't see why not. I suppose it could be something like:

SELECT Field1
FROM YourTable
WHERE StartDate = CURRENT_DATE - DAYOFWEEK(CURRENT_DATE) + 1

assuming weeks start on Sunday.

[siroos12]

1 minute ago, comment said:

I don't see why not. I suppose it could be something like:

SELECT Field1
FROM YourTable
WHERE StartDate = CURRENT_DATE - DAYOFWEEK(CURRENT_DATE) + 1

assuming weeks start on Sunday.

Thanks, would you rather doing it through a relationship or ExecuteSQL ?

[comment]

12 minutes ago, siroos12 said:

would you rather doing it through a relationship or ExecuteSQL ?

I can't answer that without knowing what exactly we are doing here, and - most importantly - for what purpose.

 

[birder]

1 hour ago, comment said:

I am mostly guessing here, but: 

If you define an unstored (not global) calculation field that returns the start date of the current week, and use it as a matchfield in a self-join relationship, so that:

YourTable::cCurrentWeekStart = YourTable 2::StartDate

and place the Field1 field from YourTable 2 on the layout, it will show data from the first record whose StartDate matches the current week's start date.

 

Unstored calculations can be used as matchfields on "this" side of the relationship (i.e. the side that requests the related records, not the one that supplies them).

 

Thank you so much.  Having the unstored calc on "this" side did the trick.  Your "guessing" was quite accurate!