Pulling Unique elements from column into column in another table

[Cerosilva]

HI,

Ok so i have two tables,

Test Orderbook which tracks the parts and their quantities and prices, bought and sold at.
Test StockBook in which i want to be able to find the current stock balance as well as total item bought and sold itemwise.

Query one:
I need the unique elements from the part name column in the Order book to auto-populate in the Name column in the Stockbook.

Query Two:
I then need to use the Rollup option item wise to pull info for the adjoining columns from the orderbook.

[Darren_Murphy]

You can do it like this:

• All the below should be done in your Test Stockbook table
• Add enough rows in the table to cover the total number of unique parts
• Start by creating the 3 columns described in the 2nd use case in this post.
• The above should result in a column that is numbered starting at 0.
• Now add a Lookup column. This column should target the Part Name column in the Test OrderBook table.
• Next, you need a Unique Elements column. This one should target the previous column.
• Now, a single value column. This one should take “From Start → Row Index” from the Unique Elements column.
• If you’ve done everything correctly so far, you should now have a column that contains a list of unique Part Names. You can now use this column to create a multiple relation back to your Test OrderBook table, matching the Part Names in each table.
• To get the rest of the values:
  – In Stock: not enough information to say. Where is the starting stock level stored?
  – Total Purchased: use a rollup → count via the multiple relation
  – Amt Purchased: use a rollup → sum → Purchase Price via the multiple relation
  – Total Sold/Amt Sold: not sure which columns in the Test OrderBook table should be used for those?

[Cerosilva]

Hi Darren,

I seem to be getting stuck at this step.

@Darren_Murphy

Now, a single value column. This one should take “From Start → Row Index” from the Unique Elements column.

I’m adding the screenshots of what I was able to do so far.

This is the over all screenshot of all the columns.

I managed to get the 12 Part names pulled up from the order book.

And the unique names as well.

But somehow, even though I’ve selected the single value column, it’s still pulling all the unique names again, instead of individually.

If you could see where I’m making an error, I’d be much obliged.

[Darren_Murphy]

The error is below:

When you select the “From” value, you should see the Unique Names column listed separately near the top. Choose that instead of Test StockBook → Unique names

[Cerosilva]

Ok, got that but there’s a problem.

When i select ‘from start’. it defaults to first. and then show only the first in all the rows.

[Darren_Murphy]

Yes, it will default to First. But you can change that.

[Cerosilva]

How?

I tried it but it’s not refreshing

If i try first, last, from end, it just gives the same in all the rows.

But not in correct sequence.

[Darren_Murphy]

Click on the down arrow next to First, and choose “From Start”

@Darren_Murphy

Now, a single value column. This one should take “From Start → Row Index” from the Unique Elements column.

[Cerosilva]

Ah, My bad.

i got it now.

I was not selecting the Row Index as custom and just leaving it at the default 0.

It’s working now.

Sorry for the trouble.

[Darren_Murphy]

Great

Let us know how you get on with the rest of it.