Hi,
My main table (Table Builder) is a list of all matched posted documents to a purchase order.
The table extracts information regarding posted invoices from the "Purch. Inv. Header" and "Purch. Inv. Line" tables.
However, whenever the main table ("Purchase Order Matching") finds a credit memo, I need to extract the equivalent information for credit memos ("Purch. Cr. Memo Hdr." and "Purch. Cr. Memo Line" tables), ideally in the same report.
I have created Helper columns to identify from the main table whether this is a invoice or credit memo, and it returns the table I'd need. This is the formula for the new field HelperLine:
=@NP("Formula","=IF([@[Document Type]]=""Posted Purchase Invoice"",""Purch. Inv. Line"",""Purch. Cr. Memo Line"")")
What I think I now need to do is to replace every NL("LinkField","Purch. Inv. Line") with something like NL("LinkField",[@[HelperLine]]).
(Note to self: I also need to ensure that the Links row includes links to both "Purch. Inv. Line" and "Purch. Cr. Memo Line.")
My problem is I can't get Excel or Jet to accept the 'floating named' formula for the LinkField. I've tried:
- =@NL("LinkField","[@[HelperLine]]") - this doesn't work because [@[HelperLine]] isn't a field within Business Central.
- =@NL("LinkField",[@[HelperLine]]) - Excel won't even let me press enter to accept this version.
- =@NP("Formula","INDIRECT("NL(""LinkField"","&[@[HelperLine]]&")") - Excel and Table both accept this in Design mode, but I get a technical error message when this is run.
Can anyone tell me the correct syntax for what I'm trying to achieve?
Thanks