Creating a Multi Column Sum from a String

1

I’m sure someone can help me through this problem that has overwhelmed my capabilities.

I have a work crew of up to 2,000 people that we provide meals for over a period of up to 21 days. I would like to store a representation of each person’s meal entitlements as a simple string in their crew record. See Source in the example below.

From this source table I would like to build a table for the kitchen to determine total breakfast, lunch, and dinner numbers on a given date. See Expected Results in the example.

The rest of the system I have built codes the meal data into groups of three. Each group represents a day (starting from a given start date), with each place in the group representing B, L, or D; 1 for true and 0 for false. It won’t to hard to refactor if there’s a better way to form the string.

I intend to rebuild the results table on a nightly basis via an automation for performance reasons but it would be great if if anyone had any suggestions regarding realtime updates without a serious performance hit.

Thank you all in advance…

2

You don’t say it directly, but I guess you are trying to work around the fact that Coda cannot handle large data sets?

Your approach sounds like a smart idea to work around that limitation, but it seems to me that it will be quite complex and unintuitive to work with and maintain.

Have you considered using Xano or something similar as a backend to store all your data properly normalized with a simpler structure and just sync a few days to Coda with the Xano pack?

In that way you can have the best of both worlds.

Hope this helps,

Pablo

3

This functionality forms part of a larger crew accreditation and crew management system which I’m redeveloping with a React frontend / Xano backend after this season’s event run. Coda’s been fantastic to get this project off the ground to develop the business logic and reporting functions, but the falls down in the creation of public facing user portals. Perhaps the indications of a more app tuned modality in the new roadmap will go some way to address these shortcomings.

Irrespective of the underlying stack it seems wasteful to create a 42,000 row table (2,000 crew x 21 days) when one can encode a string into each crew record. To the problem at hand, while its easy to the encode the meal entitlements per crew member, and decode back to dates and checkboxes for B, L, and D, it’s doing my head in to just sum all the 1’s in position 1 in a series of strings.

4

regardless of WHY you wish to represent your data in this way, there is a simple formula that will give you the result you require…

i will assume the 3 binary digits correspond to B, L, D respectively (Breakfast, Lunch, Dinner).

further, i will assume the column MealData is stored as a text on each row of the ExpactedResults table.

the formula for column B is then…

MealData  
  .Slice(2,-2)    // strip off the brackets
  .Split(',')     // split into groups 
  .ForEach(       // for each group
    currentvalue
    .Middle(1,1)  // take the 1st digit
    .ToNumber()   // convert to number
  )               // next group
  .Sum()          // sum the digits

the formula for L is similar

MealData  
  .Slice(2,-2)    // strip off the brackets
  .Split(',')     // split into groups 
  .ForEach(       // for each group
    currentvalue
    .Middle(2,1)  // take the 2nd digit
    .ToNumber()   // convert to number
  )               // next group
  .Sum()          // sum the digits

and the formula for D is

MealData  
  .Slice(2,-2)    // strip off the brackets
  .Split(',')     // split into groups 
  .ForEach(       // for each group
    currentvalue
    .Middle(3,1)  // take the 3rd digit
    .ToNumber()   // convert to number
  )               // next group
  .Sum()          // sum the digits

this gives you the results you required.
it may happen that in the future when you need to process the meal counts further
that you will want to create a separate row in another table for each meal.
or that some other data structure may be required in order to process the meals data further.

but for now, i hope this gives you the results you need.

max

4 Likes
5

I would agree … if you were programming for the Voyager probes where every byte counts.

I don’t think it will penalize performance in any way to have 42000 rows in Xano, but I fear this cryptic logic will haunt you when you need to debug some weird edge case or you want to expand the functionality.

1 Like
6

Thanks for the insight Pablo,

I look forward to the freedom when I get over to Xano.

7

Thanks Max

… and if I wanted to great a row in Expected Results with Date, B, L, and D data for each group of three in the Source.Meal Data string, using a given start date?

It’s the nesting that I have difficulty with…

8

hi @Thaddeus_Robertson ,

it is an interesting approach, nevertheless the data structure as proposed would not be my preference. First the string of 3 is not a sub list (a list in a list) , which makes it a bit harder to work with. More importantly is however the data structure, the proposed outcome is very much column oriented, while Coda is row based, see below.

image
image889×957 66.2 KB

a button creates a row for each number and links it to a date (not sure if this is what you intended) and links it to the LBD logic as well, the latter permits for a count per type.

anyway, coda is row based and I would suggest to explore this path, cheers, christiaan

3 Likes
9

Thanks all for the responses, and thanks especially to @Agile_Dynamics. Using your example as a base I finally worked it out:


  Sequence(0, 22).ForEach(
    WithName(CurrentValue, day,
      RunActions(
        AddRow(
          [Meals Total],
          [Meals Total].Date, d_low + day,
          [Meals Total].B, [Tokened Crew].[Meals List].forEach(
            CurrentValue.Split(",")
              .Nth(day + 1)     
              .Middle(1, 1)       // Breakfast digit
              .ToNumber()
          ).Sum(),
          [Meals Total].L, [Tokened Crew].[Meals List].forEach(
            CurrentValue.Split(",")
              .Nth(day + 1)
              .Middle(2, 1)       // Lunch digit
              .ToNumber()
          ).Sum(),
          [Meals Total].D, [Tokened Crew].[Meals List].forEach(
            CurrentValue.Split(",")
              .Nth(day + 1)
              .Middle(3, 1)      //Dinner digit
              .ToNumber()
          ).Sum()
        )
      )
    )
  )
2 Likes
10

Nice! Glad you got it working in Coda. How performant is that with your big dataset?

11

Thanks @Rickard_Abraham

I don’t know just yet…

I really should create a table with a few thousand rows and random Meal Data strings and test that out :slight_smile:

1 Like