Help with Parent child relationship

Hi anyone who can help. I need a way to display a view to show products at parent level with a list of who has that particular product shown in the child view. At the moment I have products setup so that the user can see what they have in stock but I want to be able to show the product and who else has it in stock? I have some tables Products, Employees, Stock in, Stock Out. I want a way to display the employee who has that particular part in stock shown in the child view of the products table?

So it would kind of look like this

Parent = [Product name]
Child = List of [Employee names] and [Stock level]

thanks

Antony

I assume you are using a slice at this moment with your existing view. What condition rule are you using with that slice?

Hi Aleksi,

I currently have a view that shows products that the user logged on has in stock. I have a ref rows from the stock in, and stock out tables, with a sum that works out the stock level of the current employee logged on. it doesn’t show names of employee. i guess i can create a view from the employees table with a ref rows on the stock in and stock out but I do not want to display it that way. I need a list of products names (Parent) and then a list of employees with the stock level of that particular product as a child list. So when I view a product it has a list of employees names and the stock level, if that is possible?

at the moment I have a slice from the Products table which is called “My Stock” with a row filter condition of [Stock Level]>0. in the underlying Products table I have a ref rows on Stock in and stock out tables, and then a sum which looks like this to give me the stock level

SUM(SELECT([Stock In][Qty In], ([Booked To Email] = USEREMAIL()))) - SUM(SELECT([Stock Out][Qty Out], ([Booked Out By Email] = USEREMAIL())))

thanks

When you have a table view with products and you click one product, do you want to open a table view of your related employees OR do you want to see related list in product’s detail view?

Hi aleksi,

I want to see a list of related products in products detail view. i only need to see the employee name and the stock level they hold of that particular product that has been selected for the main view.

thanks

Antony

IF I have understood your structure correctly, you should first sum the stock level in your “Stock In” table. Then you could create a virtual list in your “Products” table with a formula like…
SELECT(Stock In[KeyColumn],[ProductID]=[_THISROW].[ProductID])

Hi Aleksi,

this worked but i get to see all of the rows of stock in. What i need is to just see one row per employee who has that particular stock item, and the qty, is this possible?

Thanks

How about grouping that inline view with the employee?

Hi Aleksi,

that just shows the rows still but with the employee name as the group heading, if you mean “Group by”. so all I did was group by the employee name, is that what you meant?

Thanks

Yes I mean Group by.

Hi Aleksi,

In that case, it still shows the rows of the Stock in transactions. Is there another approach I could use as I cannot see this working for me?

thanks

Would you please give me the account ID and app name. I believe that’s the quickest way…

Account ID = 326317
App Name = Stock Control

It seems that you have removed that virtual list? Can you put it back as you tried so it would be easier to see the affect, thanks.

Ok, now under Products as “Site Stock”. if you look at “My Stock” View its there. if you look at record Tridonic PC 1x28-33 LO DD Combo. you can see the multiple rows

thanks

Would you please close the editor. I will show you what I mean. Is that possible?

Editor now closed, thanks

For some reason Group By twice is not working with the Detail/Inline view correctly. I need to check what could be the reason for that. I believe it would be the solution in your case. Closest I could think of… I created a “list” with a virtual column. Please check it.

Thanks Aleksi, looks fine for what I need. Appreciate the help.

Did i get the sum correct in “Stock in” for stock level, as doesn’t seem to work out correctly?
I think the “Move Stock” table is throwing things out due to the fact that it wont show in the stock in virtual column due to being in a different table
Antony

I didn’t check that formula. You should probably check it… when you sync the app, all virtual columns are calculated so if the formula is correct, it should work.