Referencing a table through another table

[Tias_Muesli]

Hi,

I’ll try to make this as simple as I can: I have an app that we use to record orders. It has 3 sheets: Company, Employees, Order. The Employees and Order sheets reference the Company Sheet. When an order arrives, we select the Company then “Add Order” to record their order.

What I’d like to do is when an order arrives for the app to find the Employee linked to the Company whose title is “Buyer” and add the value of the order to the Buyer’s Cumulative Sales.

Right now, I’m updating this column manually. My problem is, I can’t seem to be able to connect the Order sheet to the Employee Sheet as there are many Employees for each Company.

Here’s a diagram which I hope might make things a little simpler to understand. The black is what I’ve currently got, the red is what I’d LIKE to happen automatically if possible.

[Suvrutt_Gurjar]

If the understanding of the requirement is correct, please try the following.

Please create a VC called say [Total Sales] in the Company table with an expression something like

SUM([Related Orders][Order Value])

[Related Orders] is the reverse reference column in the Company table. [Order Value] is the order value of each order in the “Order” table.

In the Employees table, in the [Cumulative Sales] ( assumed to be a VC) column, please have an expression something like

IFS([Employee Type]=“Buyer”, [Ref column in Employee Table that references Company Table].[Total Sales] )

If real columns need to be updated, you will need some reference actions between tables.

[Tias_Muesli]

Hi Suvrutt,

Thanks for your response. Can you please elaborate a little bit more on your last sentence because that’s exactly where my troubles are: the Cummulative Sales is a real column in the Employee sheet, but I don’t know how to connect the Employee Sheet to the Orders Sheet because the only thing they have in common is a Company Code that connects Orders to Company, and Employee to Company. There’s no key that connects Order to Employee and I don’t know how to go about creating one. I’ve read the Help section on connecting tables but they don’t say anything about how to connect a table (in my case Orders) to another (Employee) that’s referenced within another table (Company). I should stress, there are many Employees that work in each Company, but I only want the app to find the guy who is the “Buyer” within that Company.

I don’t know if this makes things clearer. If not, I’ll post a my tables so hopefully that might help things.

Regards,
Hani.

[Suvrutt_Gurjar]

Please take a look at the below sample app, that demonstrates the reference actions. These actions are in pair of actions to update referenced records in one table based on an event action that is triggered in another table.

https://www.appsheet.com/templates/This-app-shows-how-to-use-reference-actions?appGuidString=e76d2e7...

Please define the parent or initiating reference action on the event "save/ edit " of a new/ existing order in the Order table. This action in turn can have a referenced action that updates the [Cumulative Sales] column in the Employee table.

To update the employee Employee table record with the “Buyer” role based on an Order being added in the Order table, the expression for selecting the referenced rows in the Employee table will be something like

SELECT( Employee[ Key Column], AND([Employee Role]=“Buyer”, [Company]=[_THISROW].[Company]))

To update [Cumulative Sales] in the Employee table through referenced action, the expression will be something like SUM(SELECT(Order[Order Value], [Company]=[_THISROW].[Company]))