update columns based on lookup values

(0) Share

Report

Posted on by chahineatallah1

249

Hello

I have 2 dataverse tables, one for employees and one for ordered items

Ordered items table is updated based on collection in my app

And same employees table is updated based on app whnever order is placed

Now in both tables I have order ID column, this column in employees is autonumbered, whereas in ordered items its lookup

So what I want is when employees table new record is created , it should update my ordereditems table orderid column with same number from employees

For example when employee x orders items 1,2,3,4

Table employee will create x order id 1 and ordered items will have items 1,2,3,4 under order id 1 also

How to do that? My app already generates the items and employees, but how to also automatically update orderid for items to match employees order id employees table.png ordered items.png

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Luis Arellano 247 on at

Like (0)

Report
Copy link

Link copied!

Hello, you schema seems weird, why would the "order id" be at the employees table level? Do you repeat the employee for each order they place?

Putting that aside, you mentioned that the "order id" column in "odereditems" is a lookup, how did you set that up? a lookup to what table?

Is this a canvas app you are using, right? Can you use Power automate? If you have an "orders" table and that is the record being created on employee record create, can you share more details on how you are creating that "order"? How is that "orders" and "employees" tables linked?

I am not getting a clear understanding on the order this happens in your app. You mentioned that when the employee record is created it should update ordereditems (hinting that ordereditems already exist), but then, you say that when the employee orders items 1,2,3,4, it will create the order and ordereditems (hinting that the employee record exists first so, on create, it won't have any ordereditems to update).

Sorry for so many questions but will need more info to understand what you are doing and how you could achieve your goal.

Was this reply helpful? Yes No
chahineatallah1 249 on at

Like (0)

Report
Copy link

Link copied!

how would you track what each employee ordered? you need a common order id in your ordered items table and in your employee table
So the app works in this way, when an employee orders and press place order
His items will be updated in ordered items table , and at same time employee table will be updated also , that this employee ordered this order id, as one order id can have multiple items

r u good in power apps and relationships??

Was this reply helpful? Yes No
Luis Arellano 247 on at

Like (0)

Report
Copy link

Link copied!
How I have implemented this and have seen in other systems is that you have 3 tables.
Employee/Customer
Order Header
Order Details/Items

From Employee to Order header you'll have a 1:N relation ship, the header holds information like who ordered (employeed), date ordered, totals, taxes, discounts, shipping, etc.

From Order header to Order items, there is another 1:N relationship, so here you'll know the product, Order header it is linked to, item price, item discount, etc.

This is because an employee could have several orders so, you don't want to save the the "many" part or the relationship in your employee table.

When you want to report ont his information, you'll read from the Order Headers to know all the orders 1 employee has placed. If you need the items info, then you join the items table via the Order header.

Does this help?

Was this reply helpful? Yes No
chahineatallah1 249 on at

Like (0)

Report
Copy link

Link copied!

hi, no this doesnt help, i know the algorithm
what i need is when employee places an order , an email will be sent to the employee with details of the order
and at same time also an email will go to manager for approval
do you have a video or "how to do it"? or blog
algorithms i know , in sql or other programming languages its easy, here how i can use relations in power automate

Was this reply helpful? Yes No