Thanks
Javed

Javed_Hussain · 10-22-2021 08:42 AM

Scenario in booking app → when I enter the number of passengers name and contact, ->While calculating the total amount-> the expression need to be like pointing to the current number of passengers * the ticket amount per person.

I am unable to write the expression and tried to use if, select and count functions but getting all passengers count in the column. I want to get the result of the exact and accurate number of passenger that’s entered in the current form.

Please look into and assist.

In this image-> For instance-> I need to get count for the first two highlighted rows where Parent Value is same

Steve

Something like this?

COUNT(
  FILTER(
    "table",
    ([Parent] = [_THISROW].[Parent])
  )
)

Replace table with the name of the table corresponding to the worksheet in the screenshot.

Javed_Hussain

ok thanks lemme try this

Javed_Hussain

@Steve I am getting this error in expression

Steve

Replace Parent in [_THROW].[Parent] with the name of the column in the Child table that contains the Parent ID you want to match.

Javed_Hussain

Yes the column name is Parent … pls have a look-

Steve

Please post a screenshot of the columns of the Child table from Data >> Columns in the app editor.

Javed_Hussain

Hi Steve Please check →

Steve

Hmmm…

Try this instead:

COUNT(
  FILTER(
    "Child",
    ([Parent] = [_THISROW])
  )
)

Javed_Hussain

Hi @Steve Its working but its giving the count of records of the entire column,

Whereas I need to get the count of rows belongs to a particular Parent/BookID as shown in below Image →

Javed_Hussain

Hi @Steve and Team, Any update? please help …

Question(under same topic): Do we need to use session based calculation for this form ?

Marc_Dillon

Where are you running this expression from? Another record on the Child table, a record on the Parent table, or somewhere else?

What does this mean?

Javed_Hussain

Hi Marc,

I have three tables, → Parent, Child and Book , I am running the expression/formula in Column (Total Amount) under Table (Book).

Basically its a cruise ticketing app I am trying to make and I am using nested form concept in this app.

So basically, as user I open app > As a passenger I will enter the passenger details one by one suppose Mr xx and Mrs YY and save > so at the end I want like > Total amount = Number of passengers entered in the form (Multiplied with ‘*’) price of ticket eg.,1000 rupees (I will put it constant number in formula) per person.

So if you want I can share app with you so please test and assist me to do great with appsheet form app.

I would really appreciate .

Thanks
Javed

Marc_Dillon

How isBook related to Parent and/or Child? What columns do you have in Book.

Here’s my guess, Book is a child record of Child, i.e. there is a Ref type column in Book pointing to Child. If that is true, your expression could maybe be:

COUNT( FILTER(
  Child ,
  [Parent] = [_THISROW].[Child].[Parent]
) )

Javed_Hussain

Thanks @Marc_Dillon , I would like to show Parent, Child and Book tables fields > Please check >

Book Table :–>

Child Table :–>

Parent Table :–>

So the Tables are related like :–>

Parent --(Pointing to)–> Child In from Book Table I am trying calculate the Total amount (Total Amount under Book Table) = (Children Column under Parent Table) Number of passengers entered in the form (Multiplied with ‘*’) * price of ticket eg.,1000 rupees (I will put it constant number in formula) per person.

(Passenger details are captured in Child Table) its nested under Parent Table .

Please check the above images for illustrations of the Tables used and their ref.

Marc_Dillon

So maybe Book is a grandparent record here?

Why do you have a REF_ROWS expression in Book[BookID]? That’s probably the wrong way to go here.

Perhaps make these changes:

at which point, your expression would maybe just be:

COUNT( [Related Parents][Children] )

Don’t take the above as absolute though, your data setup is still a bit confusing. Is the relationship between Book and Parent 1-to-1? If so, why aren’t they just the same record? Why are your tables named “parent” and “child” anyways, shouldn’t you give them a more descriptive name?

Javed_Hussain

Thanks Marc,

I edited my previous reply, please check and assist.

Javed_Hussain

Marc, is there any other way I can explain my app scenario ? so you can understand it better and I can get a clear help on this from you please .

Javed_Hussain

Hi Marc,

Please check like From Child Table I need to get the count of Rows for which the BookID is same >

So for example I entered two (02) passengers details so when I saved the form it should give a count throw of 2 as under the highlighted bookID there are two passengers .

so further with that passenger count in the current form > I need to calculate the total amount for the ticket. That is like Count of Passengers (Under same Book ID) * (price of ticket per person).

I hope this explaination helps …

Marc_Dillon

I asked several questions in my previous post, which you have not responded to in any way.

further, what are the actual values in this column?

Javed_Hussain

Hi @Marc_Dillon

Answers to the Questions From your side from previous posts >

Yes Grandparent one …

Is the relationship between Book and Parent 1-to-1?
Yes because when I enter and save the details in Parent form it points to Book table where it has to calculate the Total amount for the passengers entered in the Child Form (passengers)

If so, why aren’t they just the same record?
Yes they are related with the BookID because its inter related like I can explain you from my app more clearly (just want to explain the flow of the app usage)

Why are your tables named “parent” and “child” anyways, shouldn’t you give them a more descriptive name? Yes I can, however I would like to explain >

Book A Ride View > (parent form view) pointed to Parent Table > starts with select Ride, Date of Ride and Add Passenger Details with button new > When clicked on New > it opens a sub form Child (Contains Passenger details) and its pointed to Child Table> In Child Sub form need to enter passenger details >Clicked Save > it takes to another View having Total Amount and other details pointed to “Book Table”.

Why do you have a REF_ROWS expression in Book [BookID]? That’s probably the wrong way to go here.

I used Ref rows Book[BookID] " to refer to Parent and Child Table as they are all inter related under which I am entering the exact expression used “REF_ROWS(“Parent”, “BookID”)”

Marc_Dillon

Your data structure appears to be quite a mess. I’d encourage you to make some larger restructuring changes.

If you have 2 Tables with a 1-1 relationship, I’d encourage you to combine these into one single Table. i.e. Book and Parent.

Here is a visual of a basic structure I’d recommend for you here, with just 2 Tables Book and Passenger (parent and child).

Also, please review these articles, I think you’re missing a few points maybe.

Javed_Hussain

Thanks @Marc_Dillon : I will try that way…

Just one thing I would like to know like how to Catch and handle form field value from one form field and use it for calculation ? Please help

Marc_Dillon

Not sure what you’re asking. This?

Javed_Hussain

yes thanks…

I need to get the count of Rows for which the BookID is same >

So for example I entered two (02) passengers details so when I saved the form it should give a count throw of 2 as under the highlighted bookID there are two passengers .

Could you please suggest to expression to get count of Rows for which the BookID is same as highlighted in screenshot

Marc_Dillon

You need to fix your data structure, you are going to run into a lot more issues if you don’t.

With that said, you can probably calculate what you want with the following expression in a Virtual Column on your Book Table.

COUNT( FILTER(
  Child ,
  [Parent] = [_THISROW].[BookID]
) )

Calculate amount for 'n' no of persons under same booking ID

Thanks Javed

Thanks
Javed