Display data from either Query or Multiple Relations

[SG_DC]

Hi everyone -

I’m having an issue where I’m struggling to pull (or filter) the correct data and display it, using either nested relations or queries. Here is the exact use case:

• I have Purchase Orders (in a POs table), which are made up and related to multiple Line Items (in a Line Items table)
• The Line Items Table stores all Line Items. Each Line Item is related to a Product (from the Products table) and has a quantity associated with it.
• In the Products table, each product has a related “Color” (from the Colors table).
• As an example, in a given Purchase Order, I might have 4 individual line items, each with a unique product. HOWEVER, some of these products share the same related color attribute. For instance, with these 4 line items, one might be related to “Blue”, one to “Green” and two to “Black”. Additionally, lets assume that in this example, the line item for Blue has a quantity of 1, line item for Green has a quantity of 1, but the two line items for Black both have quantities of 3 (so 6 total).

What I’m attempting to do is when a user is at the Purchase Order level (viewing that screen), they would see a display of the quantity of each color “group”, for instance “Purchase Order 1; includes 1 Blue, 1 Green, 6 Black”.

The best I’ve been able to do is:

1. In the Line Items table, create a Query column. In that column, first filter where Purchase Order ID is This Rows Purchase Order ID (matching it to itself), AND, filter where a lookup of Product Color ID is This Rows lookup of Product Color ID.
2. This essentially creates a group of the line items that share the same Product Color ID of the line item.
3. I then created a Rollup column called “quantity per group by color”, and sum the quantity value of the Query column.

This somewhat works in that each individual line item has a summed quantity of the total products needed in it’s same color. However - using the example above - the four line items have rolled up quantity values of 1, 1, 6, 6 respectively.

Assuming that I’m setting this up correctly, how would I then display at the PO level “1 Blue, 1 Green, 6 Black”, and specifically account for the duplicate quantity value on the second Black?

[ThinhDinh]

Thinh's Demo - Item Colors

Here’s my take on this.

Here’s the code.

function summarizeColor(jsonString) {
  // Parse the JSON string into an object
  const data = JSON.parse(jsonString);

  // Extract the poItems array from the data
  const { poItems } = data;

  // Reduce the array to an object with color totals
  const colorTotals = poItems.reduce((acc, item) => {
    const { Color, Quantity } = item;
    acc[Color] = (acc[Color] || 0) + Quantity;
    return acc;
  }, {});

  // Convert the object to an array of [color, quantity] pairs
  const colorPairs = Object.entries(colorTotals);

  // Sort the array alphabetically by color
  colorPairs.sort((a, b) => a[0].localeCompare(b[0]));

  // Map the sorted array to formatted strings
  const formattedOutput = colorPairs.map(([color, quantity]) => 
    `${quantity} ${color}`
  );

  // Join the formatted strings with commas
  return formattedOutput.join(', ');
}

return summarizeColor(p1)

[SG_DC]

Thanks so much! That is very helpful. Using your example, I was able to output the proper string with total quantities for each.

However, in the original example I used, I was sort of summarizing the use case in an attempt to simplify it a bit. I’m wondering if you can help with the exact use case, as I’m hoping to display this on the layout side with actual attributes, instead of just as a text. Here’s an exact example of my use case…

After using your version of “JSON Final” in the POs table, I have a compiled JSON column that looks like this:

  "paperStocks": [
    {
      "paperStockName": "Colorplan Rust 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/Pbr9ZoZFIlkdKC66PFen.jpg",
      "sheetsNeeded": 34
    },
    {
      "paperStockName": "Colorplan Emerald 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/RaHqVSTLTpzKs5trZRde.jpg",
      "sheetsNeeded": 34
    },
    {
      "paperStockName": "Colorplan Stone 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/DStY9ubPnuNYI7LCqCjQ.jpg",
      "sheetsNeeded": 50
    },
    {
      "paperStockName": "Colorplan Real Grey 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/josQfHiNtexMP15Tp6cT.jpg",
      "sheetsNeeded": 50
    },
    {
      "paperStockName": "Colorplan Rust 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/Pbr9ZoZFIlkdKC66PFen.jpg",
      "sheetsNeeded": 7
    },
    {
      "paperStockName": "Colorplan Emerald 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/RaHqVSTLTpzKs5trZRde.jpg",
      "sheetsNeeded": 19
    },
    {
      "paperStockName": "Colorplan Stone 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/DStY9ubPnuNYI7LCqCjQ.jpg",
      "sheetsNeeded": 7
    },
    {
      "paperStockName": "Colorplan Real Grey 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/josQfHiNtexMP15Tp6cT.jpg",
      "sheetsNeeded": 13
    },
    {
      "paperStockName": "Colorplan Rust 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/Pbr9ZoZFIlkdKC66PFen.jpg",
      "sheetsNeeded": 25
    },
    {
      "paperStockName": "Colorplan Stone 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/DStY9ubPnuNYI7LCqCjQ.jpg",
      "sheetsNeeded": 25
    },
    {
      "paperStockName": "Colorplan Real Grey 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/josQfHiNtexMP15Tp6cT.jpg",
      "sheetsNeeded": 25
    },
    {
      "paperStockName": "Colorplan Rust 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/Pbr9ZoZFIlkdKC66PFen.jpg",
      "sheetsNeeded": 9
    },
    {
      "paperStockName": "Colorplan Emerald 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/RaHqVSTLTpzKs5trZRde.jpg",
      "sheetsNeeded": 9
    },
    {
      "paperStockName": "Colorplan Stone 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/DStY9ubPnuNYI7LCqCjQ.jpg",
      "sheetsNeeded": 9
    }
  ]
}

Using your javascript - slightly modifying it - I’m able to have that output as a string, where it correctly combines all of the likeminded “colors” (by paperStockName in my case). However, instead, I modified the code to output as JSON, with the result being:

{
  "paperStocks": [
    {
      "paperStockName": "Colorplan Rust 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/Pbr9ZoZFIlkdKC66PFen.jpg",
      "sheetsNeeded": 75
    },
    {
      "paperStockName": "Colorplan Emerald 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/RaHqVSTLTpzKs5trZRde.jpg",
      "sheetsNeeded": 62
    },
    {
      "paperStockName": "Colorplan Stone 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/DStY9ubPnuNYI7LCqCjQ.jpg",
      "sheetsNeeded": 91
    },
    {
      "paperStockName": "Colorplan Real Grey 130# - 17.5 pt",
      "paperSwatchColor": "https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/15ddj7K6XZ6W5o75fhxq/pub/josQfHiNtexMP15Tp6cT.jpg",
      "sheetsNeeded": 88
    }
  ]
}

Now that I have that compiled final JSON column for each PO, do you know of a way to display this as a list collection (or some other way) on the layout side of the PO screen?

Found this thread but couldn’t quite figure out how to make it work.

[ThinhDinh]

Transpose Columns to an Inline List: The Miracle Method

I’m often asked how to take columns of data (say form responses) and display them in an inline list. Here’s one way to do it—the MIRACLE METHOD. [Glide: Transpose Data Columns into an Inline List with the MIRACLE METHOD]

This is the method you need. It’s an old thread, but would give you the right ideas to that. Basically create a new table, get the JSON over there, add as many rows as you need to store the JSON elements, and finally parse them using Query JSON columns.

[SG_DC]

Worked perfectly! Thanks so much. I will reuse this method across many different instances now.

One follow up though: has anyone ever figured out how to have the number of rows in that helper table automatically increased based on some other number?

For instance, using my use case as an example, I have a separate table of paper stock colors. Theoretically, as long as this helper table has an equal or greater number of rows to that table, then I’ll never really need to come back and manually add rows.

I suppose I could just manually add like 100 rows when the table is created, because in my case we’ll never utilize more than 5-10 per PO. So we’ll always have more than enough rows in that helper table. Just wondering if there’s a more elegant solution.

Thanks again!

[ThinhDinh]

@SG_DC

I suppose I could just manually add like 100 rows when the table is created

Yes, this is the way to do it. Just add a huge number of rows and forget about it.