Table A: different invoices
Table B: summary of invoices by invoice date (via “link to another record”)
Table C: Share view of table B (synchronized).
I am concerned with the invoice amounts:
Via link to table A, table B shows different invoice amounts from different invoices for a given day (ex: on 1/1/2020 we have invoices, for 100, 200 and 500$; so in total 800$).
Now I would like to calculate this sum in table C via a rollup field.
The problem: Due to the synchronization, the invoice amounts are displayed as text and I cannot offset them against each other. Does anyone have a solution?
Can I make presettings so that the formatting of the fields is retained during a synchronization?
That’s a common issue (converting lookup to text via sync)
in my projects, in one case (link converted to text), I solved it by automation, added extra linked field
and auto-update it according to synced text value.
other case similar to yours, i need a currency in target table,. While lookup sync it to text.
just found a way to do it - i created a formula in source field taking number from currency, and sync it instead of currency field. result is a number, and it’s ok for me, at least i can use math filters.
i don’t know if the rollup allows mix currency and numbers, so you should just try it on a test table without a sync. i think it’s possible, but you’ll get the number at final.
*applying text formulas (like “cut $ at the left of number”) to lookups may behave strange, because lookups are arrays in real. Sometimes it needs “concatenate(x)” instead of “x” to convert to text.
i think, RIGHT(X,LEN(X)-1) may fail, same as REPLACE, but SUBSTITUTE should work.
Finnaly, result should be wrapped in VALUE() to get number.