Copying or modifying rows from CrossDoc to table

1

I can’t seem to get a crossdoc table + a canvas button with AddOrModifyRows to work as intended, so after about 10 hours of trial and error, finding various other posts that describe sort-of-but-not-really-the-same-thing, and maxing out my monthly cross doc actions with no success, i’m officially calling in community support :slight_smile:

I have two separate docs, one for my personal finance tracking and one for my business. The business is used to generate and track invoices (rows). I would like those invoices to get automatically added as new transactions in my personal doc, by bringing in a cross-doc table of all invoices (done), and then using a canvas button with AddOrModify to automatically “sync” the two- adding new invoices when available, and updating existing invoice rows when things like the status change.

I’ve been able to make this setup work as intended 90% of the way… but only when adding rows, I cannot seem to get the formula to lookup and “modify” existing transactions when they already exist. Using variables like CurrentValue or RowURL as described in various help articles do not seem to work- the best i can get is a “hard” copy from one table to another, when i’m hoping to have them “sync” and be kept up to date by pressing this canvas button.

Can anyone advise on what i’m missing that’s preventing “modify” from working? Below is the formula i’m using for the button that’s sort-of working, happy to share the doc directly if helpful. “Invoice = [Invoice Number]” is my latest attempt at trying to get it to match, after unsuccessfully trying Current value, RowURL, and other options:

[🗄️ 2022 LNI Invoices]
  .FormulaMap(
    AddOrModifyRows(
      [View of Transactions],
      Invoice=[Invoice Number],
      Date,
      [Date Billed],
      Status,
      Status,
      Amount,
      [Invoice Amt],
      Expense,
      [💵 LNI Freelance Income],
      Account,
      [💰 LNI Checking],Description,(Concatenate("Inv."," ",
    [🗄️ 2022 LNI Invoices].Invoice,
    " ",[🗄️ 2022 LNI Invoices].Description," ",
    
    (Totext([🗄️ 2022 LNI Invoices].Client)),
    " ",
    "Billed",
    " ",
    [🗄️ 2022 LNI Invoices].[Date Billed]
    ))
  ))
2

Hi @Alex_Parks,
as you have experienced yourself, Cross-doc sometimes makes things a bit more complicated… :slight_smile:

When checking data value - especially they are not basic datatypes - we might occur in unexpected behaviors.
Your formula doesn’t seem wrong per se, but it would help to have access to your docs (or a simplified version with the same issue): is it possible?

Thank you!

3

Hey @Alex_Parks are you having this error?