I wonder what I'm doing differently. I'm also logging in as admin and I created a new report that shows all transaction lines and there are transaction line id's from 1-14, but no zeroes. You're using the field {TRANSACTION_LINES.TRANSACTION_LINE_ID}, correct? I am using the Canadian version ... not sure why that would…

Well, after some discussion it seems like it has something to do with my table joins. I'll post an update when I figure it out.

I don't see parent items bought anywhere on the customer table. Where is that?

That would be terrific!

I just used MS Access to join the big list to the one from Crystal. It's done. Whew. I will look at those reports though. Thanks.

That does seem strange. The field is populated by the system though, not the user.

Guess I was wrong. Using this formula, I get a 'field not found' error. max({transaction.paymentmethod}) keep(dense_rank last order by {trandate}) I'm going to start a new thread for this.

In looking more closely, it seems as though the payment type field is only populated for one transaction line per transaction. That makes pulling the data out difficult. It seems to me that this field should have been on the transaction table and not transaction lines since it seems to be used only once per transaction.

Nothing on the class level though, eh? I'd need to individually select over 3000 items to make this work :)

The more I fix, the worse it gets ... Adding Main Line = Yes to the criteria makes the payment method work but then I can't add criteria like item is 'sub of'. Doing that returns no results. This is making me mental.

I'm just thinking of cash sales and payments since that's all we use.

It seems there is an issue with this field when viewed via the ODBC connection. There's no data in it. Argh!!! Does anyone know the name of the field in Netsuite so I can try a formula?

found it: transaction.paymentmethod

Evan! You're back! Thanks.

Congrats on resurrecting a ten-year old thread! There should be a special badge for that. :) We stopped using the ODBC connection quite a few years ago and haven't had the need to report on that field since. Wish I could be more help.

This actually seems to work: max({paymentmethod}) keep(dense_rank last order by {trandate}) I think perhaps because this is a transactions search, you don't need to specify the table, just the field.

Thanks Evan.

So very very close. I can't seem to add customer address fields to the search. When I add customer.addr1 or customer.defaultaddress or customer.addrtext they all say invalidf field. I'd like to grab the fields from the default shipping address.

It does show the customer name, yes. I didn't realize I could use the formulas to pull data from other 'tables'. I assumed that if it was a transaction search I would be limited to the transaction fields. I'll give that a try and let you know how it goes.

Thanks for the heads-up.

That works. Can I also access the seperate fields like addr1, addr2, city etc?

Because I need to show other fields from the customer record on the search (like the salutation and the address) and I need to show item fields as well. The reason I'm doing this is to create a mailing list for a mailing house. There will be a letter that goes out that says "Evan, thanks for purchasing the bassomatic 3000…

It works great. Thanks again.

Wow ... that's service! I'll give it a try on Friday.

That's great. Where can I get a list of all the field IDs? I'd like to know what the id for total amount purchased is (if it even exists).

I am now! :) That looks like what I need so far. I'll finish it up and let you know how it goes.

I was wrong ... it's not working. For items that have been purchased by more than one customer, all customers are listed, not just the most recent. Here's a screen cap of my results: Edit: Supporter = Customer Also, it's when I add an item field (like item:Name) and group by it that the extra customers are added. Edit2: It…

Looks like I still need more coaching Evan. I tried something simple like this: Formula(Text) - Maximum - max({entity}) keep(dense_rank last order by {trandate}) Formula(Text) - - max({item.custitemcountry}) keep(dense_rank last order by {trandate}) This returns only the customer name (no country). If I group the country…

This does the trick. (decode({custrecordstatus},'SP','1','0')) - (decode({custrecordfdr},'T','1','0'))

I needed to put ' ' around the field values and the 1. All fixed and works well. This is a real time saver for us. Thanks again.