Jump to content

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


GC GYM
 Share

This topic is 2811 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

"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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

"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?

Link to comment
Share on other sites

"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..

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 2811 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.