Help with Parent child relationship

[AntonyCole1979]

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

[Aleksi]

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

[AntonyCole1979]

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

[Aleksi]

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?

[AntonyCole1979]

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

[Aleksi]

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])

[AntonyCole1979]

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

[Aleksi]

How about grouping that inline view with the employee?

[AntonyCole1979]

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

[Aleksi]

Yes I mean Group by.

[AntonyCole1979]

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

[Aleksi]

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

[AntonyCole1979]

Account ID = 326317
App Name = Stock Control

[Aleksi]

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.

[AntonyCole1979]

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

[Aleksi]

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

[AntonyCole1979]

Editor now closed, thanks

[Aleksi]

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.

[AntonyCole1979]

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

[Aleksi]

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.

[AntonyCole1979]

Hi Aleksi,

I don’t think the way I have structured it this way it is going to work as when I move stock it moves from one person to the other in the move stock table and I have sums to work out the value based on who it moves from and to based in that table. This means it will not show under Stock in virtual columns you have created for me. Do you think it would be better to have a “move from” column in the stock in table so that the virtual columns you created will show employees who have the stock?

[Aleksi]

Sorry, but it is little bit difficult to give you the correct answer because I should go deep into your app so I could understand your whole process. Inventory apps are not the most easiest one in generally.

[AntonyCole1979]

Are you able to look at it?

Thanks

I also have the sum incorrect, can you see where I have gone wrong?

SUM(SELECT(Stock In[Qty In],[Booked To]=[Booked To]))- SUM(SELECT([Related Stock Out][Qty Out],[Booked Out By]=[Booked Out By]))- SUM(SELECT([Related Moved Stock][Move Qty], ([Move From] = [Move From])))+ SUM(SELECT([Related Moved Stock][Move Qty], ([Move To] = [Move To])))

[Aleksi]

The best way you can test your expression is if you do it one by one. Then it’s easier to say if that part is correct or not. In generally the syntax is probably not correct because you have this [Booked To]=[Booked To]. It’s not a real evaluation because the result is always true. It would be the same if you type it like 1=1. If you have used the same column name in both tables, it should be something like…
SUM(SELECT(Stock In[Qty In],[Booked To]=[_THISROW].[Booked To]))

[AntonyCole1979]

Thanks, I will try it now

[AntonyCole1979]

ok, so that just added up every row not by Product, but by employee name. Do I have a ref wrong?
My key is “in ID”

[AntonyCole1979]

Hi @Aleksi,

I managed to get my sums to work and the formula you did to display who has stock and the amount. The issue with this is with my “Moved Stock” table. In this table, i have 2 columns “Move From” and “Move To”, also “Move Qty”. If that employee hasn’t had stock of that item i.e exists in the “Stock In” table, the sums don’t work and also they will not show up in the formula you did because it is based on entries of the “Stock In” table. Is there a way around this?

My sums for out the stock levels are:
SUM(SELECT(Stock In[Qty In],AND([Product Name]=[_THISROW].[Product Name],[Booked To]=[_THISROW].[Booked To])))-SUM(SELECT([Related Stock Out][Qty Out],AND([Product Name]=[_THISROW].[Product Name],[Booked Out By]=[_THISROW].[Booked To])))-SUM(SELECT([Related Moved Stock][Move Qty],AND([Product Name]=[_THISROW].[Product Name],[Move From]=[_THISROW].[Booked To])))+SUM(SELECT([Related Moved Stock][Move Qty],AND([Product Name]=[_THISROW].[Product Name],[Move To]=[_THISROW].[Booked To])))

seems long winded but it works, almost!

[Aleksi]

First in generally… If I have understood your workflow correctly, you would not need to type [Product Name]=[_THISROW].[Product Name] if you are already using the syntax like [Related Stock Out][Qty Out] because it’s already a list of related products and their quantities. Are you able to do the same with this SUM(SELECT(Stock In[Qty In],…)) as well? Like [Related Stock In][Qty In]?

If your employee doesn’t have any “Stock In”, but products from “Move To”, should your sum calculation cover that situation as well?

[AntonyCole1979]

Hi Aleksi,

Yes to the first question. I will try changing the sums.
Yes to the second question and this is why. The stock is generally van stock which employees would keep on their vans, but I do also have a central store area which I would need to move stock from there to their vans. Also if someone hasn’t had stock of something before and they need to move from employee to employee, or from central store to employee then at present the sums would not work. I would have to create a blank entry for each product name and employee in the “Stock In” table for it to work, and that’s a lot of entries, especially when new stock gets added. I would need to do this process again and again.

[Aleksi]

Isn’t your formula already doing that? I mean… SUM(SELECT([Related Moved Stock][Move Qty],[Move To]=[_THISROW].[Booked To]))

[AntonyCole1979]

Yes, but as you suggested in the previous post i may have a situation where the employee doesn’t have any items in “Stock In” table but I will need to move items to them from someone else “Move” table. Sums will not work out in “Stock Level” unless “Product Name” and “Employee Name” exist in the “Stock In” table.
I have also changed the sums which work apart from on the “Stock In” table. I need to refer to also the “product name” in the select expression for it to work otherwise it just adds up every entry from the employee.

[Aleksi]

My only advice is then that you need to sum those records from “Moved Stock” table as well.

[AntonyCole1979]

Thanks Aleksi,

Will give it a try. What about this formula CONCATENATE([BOOKED TO]," ",[STOCK LEVEL]). Can I combine this with the moved stock to show also in the products table, as you did yesterday?

[Aleksi]

Yes you can do that as well.

[AntonyCole1979]

How would I combine the 2 to show only one column in products table. Current expression looks like this
SUBSTITUTE(CONCATENATE(SELECT(Stock In[Stock by Employee],[Product Name]=[_THISROW].[Product Name],TRUE))," , “,CONCATENATE(”
"))

[Aleksi]

Before doing that, let me find out why the grouping twice is not working correctly with the inline view. If we can fix that issue, it’s an easier way in your case.

[AntonyCole1979]

Ok, do I need to come out of the app now?

[Aleksi]

No, that’s not needed.

[AntonyCole1979]

Hi @Aleksi,

Thanks for your help but I think I’m going to restructure the app slightly as the whole stock moving process is a bit clunky.
Would you know if I’m able to copy to a row twice from a single action button?
I’m thinking of using 1 table to move stock in and out, but with the move part I’m going to need to create 2 rows 1 for the “move to” and 1 for the “move from”. I would have a “status” column with values set like “move from”, “move to”, "Stock in, “stock out” each time I hit an action button for each part of the process?

Thanks

[AntonyCole1979]

Ok thanks