I’m fairly new to Phocas, but have watched many webinars and searched through the Phocas documentation to find a way to have transactions be the lowest level in a nested grid. I’m starting to think it is not a feature within Phocas, but wanted to see if I was correct in that thinking. What I’m attempting to do is have our sales reps nested with customer ship to, items, then order # for a given month. Has anyone found a way to do this? Or is this something I need to put in the Phocas suggestion area?
Hey @lbullard90 -
If I’m understanding correctly, you’re looking to have a Nested table with 4 levels with (from highest to lowest level of drop-downs): Sales Rep → Ship To → Items → Order#. I’m guessing you mean an actual sales order# by “Order#”, as well, which I imagine is currently a Transaction-level property, and that’s why you can’t pull it into a Nested table with other Dimensions. The use case I’m imagining is to see which orders a specific item is on for a specific customer, rolled up all under your assigned sales reps.
If that’s all the case, one solution on the DB Design side might be to create a duplicate order# column on the transaction data with a transform statement. I’ve done this many times - if your actual column currently mapped as a Property is called Order#, then your statement for the transform column would just be [Order#]. You could then map that new duplicate column as a Dimension, which should then allow you to use that as the lowest level of your Nested table.
As an example, here is my invoice copy column from my Sales database, which is mapped to an Invoice# dimension:
Hope this helps!
Tim
1 Like
@tim.janes you understand correctly! We do have the actual Sales Order # in Transaction mode. So I’m not able to drop down to that level when I am in Period mode creating a nested grid.
I have passed along to information to my admin to have them do exactly as you have explained, as this should be the perfect solution for my analysis purposes. I’ll let you know how it goes.
I appreciate your help!
1 Like
An alternative workaround idea: If your ERP system allows, perhaps you could use hyperlinks to take a user to the transaction in your ERP.
2 Likes