How to pass a reference Column's Objects to a Pack

Making Packs
1

I’m trying to build an invoicing Pack to interface with our payment system (ChargeDesk) to product line item invoices. In my working doc I use a reference column to contain all the related rows from a separate line item table (Service, Quantity, Unit Price, Total Price). I’m not trying to figure out how I can pass those through an action formula to my Pack to then add them to the invoice? Any guidance would be greatly appreciated.

2

Hi @Ron_Gerrans3 - Sounds like an interesting Pack! We currently don’t support passing entire rows to a Pack formula, but there are a few options:

  1. First serialize the row into a string, and then use a StringArray parameter to pass those serialized values into the formula. You could invent your own serialization format or use something standard like JSON.

  2. Use a series of SparseStringArray and SparseNumberArray parameters, each of which captures an entire column of data. Then in your Pack use an index to “join” those values across the different parameters. You can see a sample using that approach here:

3

Thanks @Eric_Koleda

Actually, it’s more complicated than that. I have a column (Line Items) in my Invoice table where each cell is multiple rows from my Line Item table. I ended up:

  • creating new columns in my Invoice table for each of the sub values (ProductID, Quantity, Price) where I created a list of all the row values for each of those columns from the “Line Items” table (ProductIDs, Quantities, Prices were each is a list of the values for all the line items).
  • Then in my Pack I set the parameters to Sparse*Array for each of those three columns
  • Then in my execute I stepped through each of the three variables in order to align them to the appropriate line numbers:
for (let i = 0; i < productIDs.length; i++) {
  formData[`lines[${i}][product_id]`] = productIDs[i];
  formData[`lines[${i}][quantity]`] = String(quantities[i]);
  formData[`lines[${i}][amount]`] = String(prices[i]);
  formData[`lines[${i}][currency]`] = 'USD';
};

Solves the problem but results in three new columns (though will likely just move the list formulas into my action formula parameters)

Now I’m starting to research to see if there is any simple way to maybe convert the “Line Items” column in the Invoices table to a json string and pass that through a parameter as a single string and then I can just parse that json in the pack?? Though i ever decided to bundle this up for others, my current approach is more flexible since the column names wouldn’t be an issue… Will have to think about that.

EDIT: So looked at the example you shared and that looks like basically what I ended up doing. Thanks

4

This is what I was imagining, using a formula to create a “virtual” column of data, although sometimes it’s nice to create a real column so you can verify the intermediary results (and then just hide the column).

I agree, for a small number of columns I think this is a fine way to go, and makes it clear what the inputs are.

1 Like