I'm trying to pull a record from my data warehouse. Here's my initial attempt.
=NL("Rows",
"Unposted Purchase Invoice and Credits",
,
"Document Type Description","Invoice",
"Invoice On Hold",1,
)
However, I only want to pull the first record from "Unposted Purchase Invoice and Credits" for each document no, so I add a filter:
"Line No",10000
However, some invoices don't have a line 10000, but instead 20000 as their minimum. So I'm trying to add a nested NL(First) filter.
"Line No","=NL(""First"",
""Unposted Purchase Invoice and Credits"",
""Line No"",
""Company"",NF(,""Company""),
""Document No"",NF(""Document No"")
)
My full formula, fully nested is:
=NL("Rows",
"Unposted Purchase Invoice and Credits",
,
"Document Type Description","Invoice",
"Invoice On Hold",1,
"Line No","=NL(""First"",
""Unposted Purchase Invoice and Credits"",
""Line No"",
""Company"",NF(,""Company""),
""Document No"",NF(""Document No"")
)"
)
It's the inclusion of the nested NL which is causing Jet to fail with the message: Invalid filter Invalid filter "=NL("First","Unposted Purchase Invoice and Credits","Line No","Company",NF(,"Company"),"Document No",NF("Document No"))".
Is there a way to nest the NL(First) properly within the NL(Rows)?
