Is there a way to dynamically split out numbers into a seperate table?

I’m building an app where users in a ceramic studio can log their times, and other costs for each ceramic to be able to see the full cost of their work.
This is done via an elaborate form which is depended on what type of process each ceramic undergoes.

Each ceramic logs therefor ends up with a time cost for the process undertaken and possible a material or firing cost depending is the particular process requires that.

  • Time: $20 material: $ 3
  • Time: $5
  • Time: $7 Firing: $ 0.3
  • Time: $4 Firing: $ 0.5
  • Time: $3 material: $ 1

Each ceramic is in one table and each log is in another table. I have a relationship between from the ceramics table to the log table.

Now I thought I could just make a radial chart on each ceramic that would show the distribution of time, material and firing cost, however I now realise that these costs are not neatly separated on seperate rows. Fx a material and a firing cost will always have a time cost associated with it.

I have this ide that it might somehow be possible to tally up the categories in a separate table with time, material and firing on its own row. But this might just be me dreaming.
Is there somehow a trick to split out these costs so they’ll be able show in a chart considering the categories are static as in time, material and firing?

Yep, sounds like a helper table might be just the ticket here.

Would you mind adding a screen shot of each of the tables involved so I can get a better visual?

1 Like

Ahh… so happy it might not just be wishful thinking :grinning:

The tables are quite massive, as they’re doing all sorts of calculations and i’m just trying to implement the last bells and whistles.

If it helps, the user only needs to see the chart when they click on a ceramic in a list component which shows the ceramics detail screen. Or when a button is clicked inside the detail screen. So its possible to use actions.

I’ve also tallied each category back into each ceramic.
So each ceramic has a column for time, material and firing. I guess we need to get those three columns into another table and onto separate rows, but have the numbers change depending what ceramic is clicked. Also if it matters, there will be multiple users using the app at the same time, so multiple users could be viewing different ceramic information at the same time.

That’s fine.

Also not a problem. This is why we have User Specific columns :slight_smile:

I think I need to see more of your LogLines table. Earlier you wrote…

Can you show me how the above looks in your LogLines table?

1 Like

Just to give some more background. The user sets up their ceramic items and then they choose between 14 different processes they can undergo and the app calculates the total average time, material and firing cost for each ceramic.

So fx a combination could be a ceramic that has the following processes:

  • Wheel Throwing
  • Trimming
  • Bisque Firing
  • Glazing
  • Glaze Firing

Wheel throwing will involve some material and time
Trimming will only involve a time cost
Bisque firing will involve a firing cost and time to load and unload the kiln.
Glazing will involve time and material cost
Glaze firing will involve firing cost and time to load and unload the kiln.

As all timings and consumption in reality differs every-time the same the ceramicist is doing the processes - (One day some items can break, take longer time or a kiln can’t be filled to the maximum) I’m trying to get closest to the optimum by finding the average of all the different processes and add them together for each type of ceramic.

I hope these can shed some light:

This one is for the process I call “Handbuild-solo” this process ultimately gives two average numbers. Average time spent making pr unit and average material pr unit.

This one is for bisque firing. It has 2 times, one average time/unit for loading the kiln, and one average time/unit for unloading the kiln. Then it also has a firing cost/unit.

Trimming only has a time cost/unit.

Slip casting has an average time/unit and average material/unit.

Then each category is rolled up to averages (and for minutes fastes and slowest for the ceramicist to see what a good and bad day looks like) back in Ceramics.

And then I’ve tallied those averages for each process up into the three categories, “Minutes”, “Material Cost” and “Firing Cost” (Note I still need to convert the minutes into a cost based on the users hourly rate, but that should be fairly straight forward).

Okay… I think I have a decent understanding now. So this is what I have in mind…

  • In your LogLines table, you’ll need to create a series of if-then-else columns. There should be one for each of your process types, and each if-then-else column should return the name of the process type if that process applies to that line. I expect you can figure out the logic for that?
  • Next create a Make Array column, that combines all of your if-then-else columns into an array.
  • The next thing you’ll need to do is create a helper table, with a column that lists each of your process types. The names that you use should be identical to those that you used in the if-then-else columns.
  • In this table you can create a multiple relation column that matches the process name with the array column in your LogLines table.
  • Next add a User Specific text column. This will hold the selected CeramicID.
  • Then a Single Value column that takes the value of the User Specific column and applies it to all rows.
  • And then create a query column. This one should target the multiple relation, and filter where CeramicID is This row → Single Value Ceramic ID
  • You should then be able to do whatever rollups you need via the Query column.

To set the CeramicID into the User Specific column:

  • Add a Single Value column to your User Profiles table that takes first->whole row from your Helper Table
  • Then on your Ceramics details screen, add a component with the following action sequence:
    – Set Column Values → User Profile → Single Value Helper Table → Ceramic ID
    – Show Detail Screen → User Profile → Single Value Helper Table

You can then use the helper table screen to build out your charts, etc.

Obviously I haven’t tested any of the above, but I think it should work.
Let me know how you get on.

1 Like

Thank you @Darren_Murphy I’ll try this later today, and get back with how it works. Never used a query column before, but I’m excited!

Ok, didn’t get very far already got stuck here:
I assume you here mean I should I need to add each process to the Min, Material and Kiln categories.

The problem is that each row most of the time will belong in two categories.
Fx Loading a kiln will both have some minutes that needs to go into the min category, but also a separate cost running the kiln, which will go into the kiln cost.

I tried to build out the above, but it gets me the each process category (Throwing, Handbuilding, Trimming), but not the 3 other categories, “Time, Material and Kiln”

The main aim with this chart is to let the user see that the time spend making a ceramic object is the biggest cost compared to all the other costs, such as materials and the firing costs and they need to make sure to get compensated for time spend and not so much the other costs.

That’s why I suggested multiple if-then-else columns. You need one if-then-else column for each of your process categories, eg.

  • 1st column
    – if this row applies to Throwing, then Throwing
  • 2nd column
    – If this row applies to Handbuilding, then Handbuilding
  • 3rd column
    – If this row applies to Time, then Time
  • etc

I can’t help you with the “this row applies to…” logic, but I assume you can figure that out.
Does that make sense?

1 Like

I see what you mean (I think), however there is no row just for Time, Material or Kiln.
I got 3 columns for each of all the various processes for Time, Material and Firing however they all reside on the same row. I’ve tried to sum it up below - as the IF/Else columns point to the various columns.

Or is it me who is missing something?

No, I’m pretty sure it’s me that’s missing something :slight_smile:
I have to admit that I’m a bit lost after looking at those two screen shots.
Not sure what to suggest from here.
If you don’t mind temporarily adding me to your team, I could take a look. If I can see the whole thing in front of me in the builder, it will be a lot easier and will most likely save us both a bunch of time.

1 Like

I for a second worried about my own sanity…:melting_face:

In short all the Loglines are split up into each their process names, but each process contains (most of the time) both a time cost and a material or firing cost. So it’s how to separate the time cost away from the two other costs so they can be compared.

Again thank you for helping me with this, its extremely generous of you.
I’ll send you an invite link now.

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.