Hi,
How can I sort a field retrieved from 2 tables (for example: field "No." from "Sales Header" and "Sales Header Archive") by a calculated field of both tables (for example: field "Amount").
When I try to sort with NL("Rows") using one of the tables and the calculated field that seems to work fine, but I can't manage to sort out the field from both tables at same time. Is it possible?
Thanks in advance
Date
Votes
9 comments
-
Jet Reports Historic Posts Hi,
Can you attach an example report to show exactly what you're wanting to sort on? Thanks!
Regards,
Hughes -
Jet Reports Historic Posts Hi,
I've attached an example of what I'm trying to achieve.
The NL formula is working, but it's only sorting the 'Purchase Headers' No. field I need to bring on the 'Purchase Header Archive' No. field as well.
Many Thanks -
Jet Reports Historic Posts Hi tmp,
If I try your example, your sorting does not work on your Purchase Headers No., but on the Amount out of you Purchase Header, because of following piece of formula:
-=NL(""Sum"";""Purchase Header"";""Amount"".
I think the min in front of your formula gives you a descending sort…
But I can not give an answer to your question how to sort on multiple tables.
Regards,
Bert -
Jet Reports Historic Posts Tmp,
As Bert said, your formula is actually set to sort by a sum of the amounts from the purchase header and purchase header archive tables. Actually, given your NL(Rows) formula is pulling all the rows from the purchase header already, there is only going to be a single amount per row anyway from the purchase header so that sum isn't even doing you any good.
So are you saying that you want a combined list of numbers from both the sales header and sales header archive sorted by the sum of the amounts from the respective table? Or are the numbers the same in both tables? If the numbers are the same and you can get them from a single table, then it should be possible, but if each table has a different set of numbers I don't think your sort is possible.
Regards,
Hughes -
Jet Reports Historic Posts Hi,
Sorry about the confusion of my last post - will try to be clearer this time.
My example is sorting purchase headers by the sum of amount of purchase header + sum of amount of purchase header archive. That's fine.
But what I need is to sort out both the purchase headers and purchase headers archive. Would it be possible to include that calculated field on a NL("Union") or something similar?
It's just odd to me if there isn't a way because this is a common request in reports. To sort documents that are held in different tables by a calculated field.
Many Thanks -
Jet Reports Historic Posts Sorry, I really don't think that's possible in Jet Essentials. NP(Union) doesn't have any notion of filters at all; it just combines 2 or more arrays of values. It's actually not a request I can remember hearing very many times to tell you the truth. The best way to achieve this would probably be to use Jet Enterprise. Then you could combine both tables along with whatever calculation you need to sort by in a warehouse table and use Jet Essentials to report off of that. If anyone else on the community has any suggestions I'm open for them, but I can't think of any other way to achieve what you're after with just Jet Essentials.
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Thanks for your help. I'll just have to sort both sets through a pivot table then.
Will keep an eye on the post just in case someone has a better idea. :)
Many Thanks -
Jet Reports Historic Posts HI,
Just come across this thread, and I can back up what Hughes said.
I have been trying to do something similar (sort on a calculated field with data pulled from two tables). After about a week of playing I have concluded that:
1) If you pull the data from a single table then you can sort from a calculated filter (based on more than one table)
2) If you combine data from two tables using a NP Union function, then calculated filters simply don't work - I can't get them to sort to even to filter properly. In this case you can only sort on the basic field in the main table.
Either way works on it's own (calculated filter, or NP union) but if they are combined it just doesn't work.
Regards,
Garry. -
Jet Reports Historic Posts Hi tmp,
Don't know if you are still monitoring this thread, but if you are I have found a solution of sorts.
What I have done is to build a Jet report which contains all of the data I need, and then use this as the source for a pivot table. Within the pivot table you can then order by more than one level of field.
The hurdle is the data source. What you need to do to get this to work is to use a dynamic range name (define it using the OFFSET command in the Name Manager) otherwise you get an error from the Pivot Table every time the Jet report runs.
This basically works for me, so thought I would pass it on in case it is of help.
Regards,
Garry.
Please sign in to leave a comment.