Olivier Gagnon NC Red Ribbon

Negative, not without ODBC or scripting of some sort.

Are you talking about the {today} operator?

Ahhhh Mmm, try casting it with to_date('1970-01-01'). Might need to specify a format... check the syntax, but I would try that

Can you remove the grouping and output Transaction: Internal ID? There are 2 possibilities that I can see - 1. Assembly components show as being sold because their parent was sold (unlikely?) 2. NS performs an outer join so you're pulling all assemblies and all inventory items that have NO transaction or sales orders When…

Using a Transaction search, this will be difficult. What I would recommend you try doing is an Item search. From there, you can join on Transactions and pull everything you needed, plus you now have access to the "Pricing..." join on the item record, from which you can pull more detailed information as well as filter on…

Use "Inventory Location", not "Location"

Hmm, let's try this cust_thing -> GROUP tranid -> MIN with a filter on the date as normal. Does that work?

Mmm yeah. I am pretty sure this search can be done, but I'd have to spend more time than I can playing with the search to get it right.

Oh, I see.... Well, I suspect to translate that exact formula basically requires the use of a dense_rank and that won't fly cause it cannot (as per my experiments) be used in criteria. But I think we can find a way around that.. Can we hard-code the date range? Or do you want to have it as a footer filter? Cause if we can…

Tick in the "to be emailed" checkbox on the Invoice.

How many transactions do you have? Maybe try returning a single record type for now, like Cash Sales only?

Oh oops sorry read your post a little too fast. It think you'll probably need to use a formula to make (what I call) a soft join. Something like "case when transaction.date>whatever or (transaction is empty and qty > 0) then 1 else 0 end" and then filter to display 1 Sorry that's not very detailed but that's from the top…

Your current data structure won't allow that. There is no actual join between item and Partner - using the same field is not good enough. You would need to add a Partner multi-select on Items and select the Partners at that point, or perhaps create a middle-table custom record to make a many-to-many association between…

Start with an Item search and then join on Transactions instead. That ought to give you all item records.

Adding a filter on Item will return, like you say, 5 rows - 1 per transaction where this item is. Item On Any Line would return the entire transaction resultset, so probably more than 5 lines. Like, if you have a PO with your item, but also 10 other items, Item On Any Line would return you 11 lines - 1 for that particular…

Not sure this is in the documentation anywhere, actually. Just learned it from trial and error and observation I guess!

I don't recall if you will see a listing on the Partner record out-of-the-box, but you should get a bunch of Partner Reports in the Reports section. Also, it's possible to create a quick Saved Search and add it to the Partner record via the sublist functionality - this would do exactly what you're looking for.

Not sure I fully understand your request, but sorting your custom records by Internal ID (highest to lowest) will give you the most recently created record first.

You're right, no good reason. You should file a case.

Yeah you need to use a formula with dense_rank(). Fun times. If an SQL Ninja like Evan drops by they can give you the right formula.

You're welcome :) Very helpful Q&A post for all, good idea to share that!

No it is not, you have to compile it yourself with a MAX(date) on a PO search.

Hi, Just so happens we have a new blog post up about this exact subject! You can check it out here.

decode( [value to verify], [value to verify against], [return value if true], [return value if false]) so it's the equivalent of saying IF [value to verify]==[value to verify against] THEN [return value if true] ELSE [return value if false]

You can do it with some formulas. Create the typical search outputting inventory location / inventory on hand Add a 1 numeric formula field per location, like this - decode({inventorylocation},'whatever you location is called',{locationquantityonhand},0) Then Group by Item and SUM your formula fields.

That's pretty complex, actually. Evan can correct my SQL but I believe it would go something like Formula (Currency) - SUM sum(decode({type},'Item Receipt', nvl({quantity},'0'),0)) - (sum(decode({type},'Invoice', nvl({quantity},'0'),0))-... etc So your building block is sum(decode({type},'Item Receipt',…

Makes sure the customer id on the custom record is "record is parent" and you won't have any problems. Besides, if the field is not record is parent, you won't bhe able to get both record types on the same search.

You could put as many as you can in one, then add a OR and stick the rest is a second filter of the same type. That might work.

Correct, but this is simply remedied with adding in the summary criteria "OR max(date) is empty"

Try the following Add these filters: Transaction...Account = your income account Transaction...Posting = true (Note: you could instead go with Transaction...Type = Invoice/Cash Sale/Credit Memo/etc. Should be about the same results) Add column Transaction...Amount (SUM) You also want to add to your existing columns GROUP…