Getting the related portal row number/record number in a self join relationship

[GC GYM]

I have a table of customers, and a self join TO, MyFamilyMembers to list all customers in their family (including themselves). Related by a family ID.

 

The TO is sorted to put them in the order that I want them to appear in the portal. The sort is by the value of the product they buy, descending.

 

I can show each family member's 'position in the family' by placing {{RecordNumber}} on the portal.

 

I want to use that 'position in the family' in a calculation but I don't know how to refer to it or retrieve it.

[GisMo]

How are you using {{RecordNumber}}?What does your calculation tell you? ..Maybe you could use a SQL statement to do you calculation.

[Fitch]

Perhaps you could use GetNthRecord.

[GC GYM]

"How are you using {{RecordNumber}}?What does your calculation tell you? ..Maybe you could use a SQL statement to do you calculation."

{{RecordNumber}} when placed on a portal row gives the portal row number i.e. it gives the Nth record via the relationship. When not in a portal it gives the record's position in a the found set.

I can also get it by adding a calc field Get(RecordNumber), but can't get my head around how to refer to this particular record via the relationship in a calc.

[GC GYM]

Perhaps you could use GetNthRecord.

Ok, but I can't  say which record to go to, that is my question; What is the record number of this record via the self join relationship?

Perhaps you could use GetNthRecord.

Ok, but I can't  say which record to go to, that is my question; What is the record number of this record via the self join relationship?

 

How are you using {{RecordNumber}}?What does your calculation tell you? ..Maybe you could use a SQL statement to do you calculation.

Haven't ever used SQL statement - will look into that, thanks.

[bcooney]

"The sort is by the value of the product they buy, descending. I can show each family member's 'position in the family' by placing {{RecordNumber}} on the portal."

 

Huh? The product they buy somehow informs their rank in the family?

[GC GYM]

"The sort is by the value of the product they buy, descending. I can show each family member's 'position in the family' by placing {{RecordNumber}} on the portal."

 

Huh? The product they buy somehow informs their rank in the family?

Yes. The reason for needing the rank is to enable a family discount to be applied, different depending on their bill.

First Family member, full price

Second family member 5% discount

Third Family member 10% discount etc.

Most expensive bill is considered the first person....and so on..

[bcooney]

So, you're designing a multi-person discount for a registration system, perhaps? Wouldn't you just need to count how many family members are already registered? Seems like you're missing tables and I cannot see why you'd need a self join on People.

[comment]

Wouldn't you just need to count how many family members are already registered?

 

I agree with Barbara here, but if you want to display the individual discounts applied to each family member, you can calculate their position in the sorted list as =

Let (
index = List ( Customers 2::CustomerID )
;
ValueCount ( Left ( index ; Position ( ¶ & index & ¶ ; ¶ & CustomerID & ¶ ; 1 ; 1 ) ) )
)

(evaluated from the context of Customers).

[GC GYM]

Thank you all for your help with this.

 

So, you're designing a multi-person discount for a registration system, perhaps? Wouldn't you just need to count how many family members are already registered? Seems like you're missing tables and I cannot see why you'd need a self join on People.

Because the discount is not based on how many family members are already registered, it is based on how many times per week they attend, therefore how much they pay. So the self-join TO lists all people in the customer's family, including themselves, in order of how much they pay, not the order they joined. I want to apply a different discount to each customer when billing, according to where they rank in this sort order.

 

Maybe to explain this better, there is no Family table in this DB. Family is established by the self-join relationship using the mother's mobile number as the key.

[bcooney]

Where are you storing Attendance? or the "Visits this Contract?"

[GC GYM]

I agree with Barbara here, but if you want to display the individual discounts applied to each family member, you can calculate their position in the sorted list as =

Let (
index = List ( Customers 2::CustomerID )
;
ValueCount ( Left ( index ; Position ( ¶ & index & ¶ ; ¶ & CustomerID & ¶ ; 1 ; 1 ) ) )
)

(evaluated from the context of Customers).

Perfect, thank you Michael, that worked.

 

Thank you all for your help with this, I have been going around in circles for a long time.

Where are you storing Attendance? or the "Visits this Contract?"

An unstored calc on the related table of Class Enrolments