Fetching Data From Another Table for Initial Value

i have two tables
1.order list
2.items

this is the order table

this is the item table

for the initial value of OrderID in Items table i want to fetch and insert the data from OrderList table where the UserEmail is equal to Useremail() .

i have tried LOOKUP() but i cant figure it out.

[OrderList].[ColumnNameYouWantTheDataFromInTheOrderTable]

1 Like

i wanted the OrderId where the UserEmail is same as UserEmail()

Try

Select(OrderList[OrderID],[UserEmail]=UserEmail()))
1 Like

i tried it and got the error “The expression is valid but its result type ‘List’ is not one of the expected types: Ref”
i also user ANY() but it didnt return any value unfortunately

By searching for records where …=UserEmail()… the result is always going to be a list. Equations that result in lists can only be in Virtual Columns. Sounds like you have it in a ‘normal’ column of type Ref

is there anything i can do about it?

I think it might be helpful to explain what it is your trying to achieve

i just want to fetch the data from OrderList table where the orderId is same as the USEREMAIL(). then i want to use that data as a initial value for orderId in Items table. i hope i made it clear enough

So when in a OrderList item you want to see all the other orders created by the same person? If so then the forumla above will do that. My question was really more about why you’d want to see all these orders…

i actually dont want that. i just wanted to show the data where their email is same as the useremail in orderlist table

Maybe I’m not understanding this. Try setting this

Select(OrderList[OrderID],[UserEmail]=UserEmail()))

as the forumla for a slice then create a table view that refers to this slice

i will try to explain one more time.
so in the table Items there is a column called OrderId which is ref to OrderId in the table OrderList.

So when i want to add a new item it will fetch the OrderId where the UserEmail is equal to UserEmail() which is a text type. i will use this OrderId which i will get from OrderList table as the initial value when i will add a new item in the Item table.

hope this makes sense

The bit I’m struggling with is this
1 = OrderList is the parent table and OrderList[OrderId] is the key. Lets say the key is “ABC123”
2 = Item is a subtable of OrderList and Item[OrderID] is the ref back up to the parent table
3 = So when you create a new Item then in this example Item[OrderID]=ABC123

I can’t fathom out what your trying to do with UserEmail() and OrderList[UserEmail]

It sounds like you want either
A = a list of OrderList created by the logged in user
B = a list of Item where OrderList[UserEmail] equals the logged in user

so the thing i wanted is that every user will have only one OrderId. so when the user adds a new item in the Item table its OrderId will be same as the users orderId in orderList table. so that i can show that these items belong to single OrderId and that OrderId belongs to that user.

for example a users OrderId in OrderList is “ABC123”
when a user adds multiple items in Items table all the Items orderID will be “ABC123” and belong to that single OrderId. as every user has unique OrderId it wont allow other user to see the items other than the user logged in. so i want to distinguish that using useremail.

also please let me know if there is better way to manage a cart system where there will be multiple item in a single order