Why data of two rows of two different table doesnot get copied in single row of third table using action

[anonymous]

Hello

I have made one property App, wherein I am stuck somewhere my data of two rows of two different tables doesn’t get copied in a single row of the third table using action. It gets copied through some formulas but data fetched from only one table is correct and data from other table is incorrect

First Let me provide you with details below

I have made 3 tables
1st One - “PSV SVC Sheet” - which is to keep records of Leads ( Enquiries for Properties ) with their details
2nd one - “Price Sheet” - which is to keep records of all properties with their details
3 rd one - “Cart Property” - which is to keep records of Properties selected for a particular lead
Means 3rd one - “Cart property” has columns which have some columns from both other two tables i.e “PSV SVC sheet” and “Price Sheet”

The Columns as below in each table

In “PSV SVC Sheet”

SRNO
Lead Data ID ( Unique ID Column for this table )
Registration Date
Title
CustomerName
Title 2
Email Id
age
Salary
Occupation
ETC

In “Price Sheet”

Srno
Project Id ( Unique ID Column for this table )
Property ID ( Unique ID Column for this table )
Country
State
City
Location
Area
Street
Property
Available / Sold
INVESTMENT PROPERTY
Project Name
ETC

In “Cart property”

Srno
Lead Data ID ( Same as PSV SVC sheet table 0f Leads )
CustomerName ( Same as PSV SVC sheet table 0f Leads )
Contact No.1 ( Same as PSV SVC sheet table 0f Leads )
Assigned to ( Same as PSV SVC sheet table 0f Leads )

CART PROPERTY ID ( Unique ID Column for this table )

Project Id ( Same as PRICE sheet table 0f Properties )
Property ID ( Same as PRICE sheet table 0f Properties )
Country State ( Same as PRICE sheet table 0f Properties )
City ( Same as PRICE sheet table 0f Properties )
Location ( Same as PRICE sheet table 0f Properties )
Area ( Same as PRICE sheet table 0f Properties )
Street ( Same as PRICE sheet table 0f Properties )
Property ( Same as PRICE sheet table 0f Properties )
Available / Sold ( Same as PRICE sheet table 0f Properties )
INVESTMENT PROPERTY ( Same as PRICE sheet table 0f Properties )

Now If you will see in “Cart property” I have taken Columns from Both above sheets
also I have made columns of UNIQUE IDS in all tables. which I have mentioned above besides names of Column
The idea is to copy Records of sorted properties of that particular lead (Enquiry) as per his requirement and Mail them or Whatsapp them

Also I have given reference of the PRICE table in PSV SVC sheet

So the process is as I go into the lead table and understand customers requirement of property and as per his requirement I check out Property into price sheet as per his need and finally, after matching his needs I select them and add to the “Cart property” table through action

Since there are three tables involved so I had to take action in two steps as read somewhere in-app sheet community. I am not a professional person and have limited knowledge of AppSheet, Still, I have managed to make this app as per my requirement.

---

In Action step 1 if you see I have done this below

Action 1: Copy PSV SVC Sheet rows to Cart Sheet for this row
​
For a record of this table
PSV SVC SHEET

The type of action to perform
Data: execute an action on a set of rows

Referenced Table
The table whose action will be executed
Price Sheet

View Definition
Referenced Rows
From the referenced table, these are the rows to act on
= filter(“Price Sheet”, true)

Action 2: Copy this row to Cart Sheet

---

In Action step 2 - I have done this below

Action 2: Copy this row to Cart Sheet
​
For a record of this table
Price Sheet

The type of action to perform
Data: add a new row to another table using values from this row

Table to add to
CART PROPERTY

Set these columns

Project Name = [Project Name]
​Project Codename =[Project Codename]
Project Id=[Project Id]
Property ID=[Property ID]
Available / Sold=[Available / Sold]
​Country=[Country]
State=[State]
City=[City
Location=[Location]
Area=[Area]
Street=[Street]
Property=[Property]
Structure=[Structure]
Const=[Const]
Possn Date=[Possn Date]
INVESTMENT PROPERTY=[INVESTMENT PROPERTY]
Unit=[Unit]
All in Price Amount=[All in Price Amount]

Lead Data ID= maxrow(“PSV SVC SHEET”, “_rownumber”)

If You See Many Columns are from Price sheet and last one “Lead Data ID” and also few such as customer name etc are from PSV Sheet ( leads sheet )

I have used that mentioned formula for LEAD DATA ID which is the unique ID of Lead table. The Next step is to go to the property and click the action 2 button to copy data of Property from the Price sheet along with Details of the Lead customer in the lead table to “CART PROPERTY”

Now I am stuck here, the issue is property data from PRICE SHEEET gets copied to CART SHEET but with wrong lead customer details from PSV SVC SHEET . I understood that this is due to the formula in Lead Data ID= maxrow(“PSV SVC SHEET”, “_rownumber”) which takes the Last lead row number clients details.

so further I changed the formula to another which was
SELECT(PSV SVC SHEET[Lead Data ID],[_rownumber]=[_THISROW].[_rownumber])
but unfortunately, this Copied lead data records according to row number of PSV SVC sheet or Row number of PRICE SHEET and not according to that particular lead under which we carted property.

Also, I tried with many other Formulas but that fetched only one table i,e PRICE Sheet Data and customer details went blank

I have Noticed that formula in LEAD DATA ID which is Unique ID column creates some Issue in fetching correct data from PSV SVC Sheet along with its selected properties details from PRICE SHEET

I hope you must have understood the whole point

Kindly help me with this

Thanks and Regards
Vijay Dama

​

[SkrOYC]

Vijaydama:

I hope you must have understood the whole point

Personally, I don’t

[Guide on How To Create a Community Post](https://community.appsheet.com/t/guide-on-how-to-create-a-community-post/35855) Tips & Tricks ?

Dear valuable members and users, For a long time I’m seeing a lot of multiple and concurrent posts, posts like novels seeming to aim the Pulitzer Price, exactly the same questions by the same user but paraphrased/re-phrased etc. Being a dedicated community member and an AppSheet developer who is reading around 2.5K posts per day, I urged myself to tip on some good advice and best practice BEFORE posting something in the community: #1 - APPSHEET DOCUMENTATION AppSheet has a very good help do…

I’ll try to take another look at this later if I get the idea clearer

[anonymous]

Hi, Thanks for Your Reply

I would like to explain this as Below

I have 2 tables - - I will call them as Source table
Table No 1 - Named as PSV SVC sheet
Table No 2- Named as PRICE SHEET

and I have Made 3rd Target table Named as CART PROPERTY

I want to add Data or Pull data through action from rows of each source table in Target Table in a single row

I have made few required common columns from both source table in Target Table

The Flow is as below

My 1st Source table i.e PSV SVC Sheet contatins data of My clients and i have also made Unique ID for that which is called as LEAD DATA ID

and mMy second source table which is PRICE SHEET contains details and Pricing of properties

Further I have Included my Second Source table - PRICE SHEET as a child table in my First table PSV SVC SHEET for the purpose whenever I enter into client detail in the first source table i can find properties for him at sameplace which i fetch ithrough Child table- PRICE SHEET i have included in it

Once i see the properties in child table my idea is to select few properties there and do an action of adding those properties in Third table which isTarget Table CART PROPERTY.

So the whole idea is to add rows of property selected from PRICE SHEET table along with Unique ID and customers other details in a Single row in third table i.e Target Table CART SHEET

For this i had gone through some readings and have Made two types of Action in Behaviour.

[ Action 1: To Copy Rows of first Source Table i.e PSV SVC Sheet rows to Third Table i.e Target Sheet - CART SHEET for this row
​

For a record of this table
PSV SVC SHEET ( first Source table )

The type of action to perform
Data: execute an action on a set of rows

Referenced Table
The table whose action will be executed

Price Sheet ( Second Source table )

View Definition
Referenced Rows
From the referenced table, these are the rows to act on
= filter(“Price Sheet”, true)

Action 2: Copy this row to Cart Sheet ]

---

And Then ACTION 2 To copy rows from second Source table i.e PRICE SHEET to Third TArget Table - CART SHEET . along with setting of columns as mentioned Below

[ Action 2: Copy this row to Cart Sheet
​
For a record of this table
Price Sheet

The type of action to perform
Data: add a new row to another table using values from this row

Table to add to
CART PROPERTY

Set these columns

Project Name = [Project Name]
​Project Codename =[Project Codename]
Project Id=[Project Id]
Property ID=[Property ID]
Available / Sold=[Available / Sold]
​Country=[Country]
State=[State]
City=[City
Location=[Location]
Area=[Area]
Street=[Street]
Property=[Property]
ETC

Lead Data ID= maxrow(“PSV SVC SHEET”, “_rownumber”)

]

Now if you see here above cloumn settings gives me option to match cloumns of Target Table with Coloumns of First source table as well as Second Source table

If You see Last Coloumn is of First Source table
Lead Data ID= maxrow(“PSV SVC SHEET”, “_rownumber”)

and other column above are of Second source table

So when i click second action, data gets added to Third sheet - Target Sheet of first source table as well as second source table but now Problem lies here as below

The formula which i put against LEAD DATA id Match cloumn is
Lead Data ID= maxrow(“PSV SVC SHEET”, “_rownumber”)
and this gives me incorrect results
Properties included are correct but Lead Data ID and Customer name it fetches in wrong. It select last entry of data

Further I tried many formulas Like Using SELECt Expression but that dosent work.
SELECT( PSV SVC SHEET[Lead Data ID],[_THISROW].[_rownumber]=[_rownumber])

Further I Used ANY Expression along with SELECT Expression as mentioned below Which works
ANY (SELECT( PSV SVC SHEET[Lead Data ID],[_THISROW].[_rownumber]=[_rownumber]))

But again Here the same issue arises that it select the Customers Details which matches the current rownumber of Target Sheet to rownumber of first Source table and Not actually the customer for which the Property was selected and Carted

I don’t understand how to fetch the same LEAD DATA ID ( Unique ID of first source table ) into target table so that same get seen under my same customer

Is there any Problem with formulas or my technique of action i really dont understand

Kindly help me with the same, i will be highly Obliged

Thanks and Regards
Vijay Dama