HI All,
I have done a search for some info on my issue, but can't find a solution, so am posting this topic.
What I need to do, is pull rows from a table with certain criteria, and also filter by a linked table.
This works fine, but when I try to sort by a field in the linked table, it does not sort correctly, and instead sorts by a field in the main table.
Here is the function I am using:=NL("Rows","job-hdr",,"job-dept",$C$2,"address-code",$C$6,"Link=","job-col","job-id","=job-id","+col-date",$C$5)So as you can see, I am pulling rows from "job-hdr" where the "job-dept" and "address-code" fields are equal to my options at run time.
I have then linked to the "job-col" table using "job-id" as the link, and have applied a filter also on "col-date".
$C$5 is a date range, and it is pulling only jobs with a col-date in that range, but it is not sorting them by this field.
It is in fact sorting them by a field called "job-disp" which is in the main "job-hdr" table.
Do I need to return the job-hdr rows, and then do another NL on that set of results and filter from there? If so, how is this achieved.
Thanks, Eds
Date
Votes
8 comments
-
Jet Reports Historic Posts Hi Eds,
You can't sort in a Link=. I'm not sure exactly how Jet would sort the results anyway. The link table can have more than 1 record for each record of the main table, so which linked record would the results be sorted by?
It is possible to use a calculated field to perform the sort either on a sum of values or on the first value on the linked table if you are using Microsoft NAV, but it doesn't look like that is the case from the table and field names.
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
What I have done, is used an NL Filter field, rather than using a Link=.
Here is what I have done:B2 =NL("Filter","job-hdr","job-id","job-dept","MFE|MFI","address-code","MARI53|MAR122")If I understand correctly, this pulls the job-id field from the job-hdr table, where the job-dept and address-code are filtered on.
Then I have another NL like this:C4 =NL("Rows","job-col",,"job-id",$B$2,"-col-date","01/07/2013..01/08/2013")So I thought this should then get all rows from the job-col table, where the job-id is pulled from cell B2, and also where the col-date is within that range.
When testing, it looked to be working, and I could change the sort on cell C4 to sort by col-date ascending or descending.
There are a couple of dates however, that are either out of order, or are not in that range. Here is the set of col-dates that were returned:01/08/2013 01/08/2013 01/08/2013 01/08/2013 31/07/2013 30/07/2013 30/07/2013 29/07/2013 29/07/2013 29/07/2013 29/07/2013 29/07/2013 18/07/2013 18/07/2013 26/07/2013 25/07/2013 25/07/2013 25/07/2013 25/07/2013 25/07/2013 24/07/2013 23/07/2013 23/07/2013 18/07/2013 22/07/2013 22/07/2013 22/07/2013 19/07/2013 19/07/2013 19/07/2013 19/07/2013 18/07/2013 18/07/2013 18/07/2013 17/07/2013 16/07/2013 16/07/2013 16/07/2013 16/07/2013 16/07/2013 16/07/2013 16/07/2013 16/07/2013 16/07/2013 15/07/2013 15/07/2013 15/07/2013 15/07/2013 15/07/2013 15/07/2013 15/07/2013 15/07/2013 15/07/2013 15/07/2013 15/07/2013 12/07/2013 12/07/2013 12/07/2013 11/07/2013 10/07/2013 10/07/2013 10/07/2013 10/07/2013 09/07/2013 08/07/2013 08/07/2013 08/07/2013 08/07/2013 08/07/2013 08/07/2013 08/07/2013 08/07/2013 05/07/2013 05/07/2013 05/07/2013 04/07/2013 04/07/2013 04/07/2013 04/07/2013 03/07/2013 03/07/2013 03/07/2013 03/07/2013 03/07/2013 03/07/2013 03/07/2013 03/07/2013 03/07/2013 03/07/2013 03/07/2013 03/07/2013 02/07/2013 02/07/2013 02/07/2013 02/07/2013 01/07/2013 01/07/2013 01/07/2013 28/06/2013 28/06/2013
Sorry it is a long list, but you can see the two at the bottom are not in the range, and at the early to mid 20's, there are a few dates that are in the wrong place.
Can you tell me if perhaps I have made a mistake with my functions?
Thanks, Eds -
Jet Reports Historic Posts Hmmm, that seems very strange. I would expect the same result you would expect. I have no idea why you're getting those values. What database are you reporting from?
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
It is a Progress OpenEdge 10.2b database. I couldn't quite wrap my head round it.
I have set the filter to show query, and this is what I get:SELECT "job-id","col-no" FROM "MULTIFRT"."PUB"."job-col" WHERE ("job-id" IN (SELECT DISTINCT "job-id" FROM "MULTIFRT"."PUB"."job-hdr" WHERE ("job-dept" = 'MFE' OR "job-dept" = 'MFI') AND ("address-code" = 'MARI53' OR "address-code" = 'MAR122'))) AND ("col-date" BETWEEN {ts '2013-07-01 00:00:00'} AND {ts '2013-08-01 00:00:00'}) ORDER BY "col-date" DESC,"job-id","col-no"
So it looks to be selecting col-no and not col-date. I am just wondering if it is becuase for each job-id, there can be a couple of entries, with a different col-no. Could that be why it is getting itself confused?
Cheers, Eds -
Jet Reports Historic Posts Hmmm, so as far as the date filtering, your query has this part near the end:
("col-date" BETWEEN {ts '2013-07-01 00:00:00'} AND {ts '2013-08-01 00:00:00'})
So I'm guessing maybe Progress OpenEdge might use a different date format, which could be why you're getting extra dates. You can adjust the format string that Jet uses for dates in the data source settings by going to the Advanced page and then to Configure literals. Unfortunately, I don't know much about Progress OpenEdge, so I don't really know what date format it is expecting, but you might be able to find out online or from your IT people.
Regards,
Hughes -
Jet Reports Historic Posts So I think I have figured it out.
In the previous attempt, I had not specified which fields I wanted the NL function to retrieve from the job-col table, so it somehow decided to only get job-id and col-no.
I changed the NL function and explicitly listed the cells I wanted to retrieve, so job-id and col-date. The SQL now looks like this:SELECT DISTINCT "job-id","col-date","col-no" FROM "MULTIFRT"."PUB"."job-col" WHERE ("job-id" IN (SELECT DISTINCT "job-id" FROM "MULTIFRT"."PUB"."job-hdr" WHERE ("job-dept" = 'MFE' OR "job-dept" = 'MFI') AND ("address-code" = 'MARI53' OR "address-code" = 'MAR122'))) AND ("col-date" BETWEEN {ts '2013-07-01 00:00:00'} AND {ts '2013-08-01 00:00:00'}) ORDER BY "col-date" DESC,"job-id","col-no"So it is the SELECT DISTINCT and the field names that have changed. This seems to have done the trick, and am now only getting dates within that range, and they all appear to be in order.
I do find it odd how sometimes if you don't tell jet to only select certain fields, rather than selecting all fields, it picks what it thinks you want.
Anyway, hopefully sorted, thanks for your help.
Eds -
Jet Reports Historic Posts Hi Eds,
Jet doesn't pick what it thinks you want; it selects the primary key of the table. In your case, the primary key was job-id and col-no. Jet uses the primary key fields to build the record key which it puts in the cell. Since the primary key values are guaranteed to be unique and guaranteed to uniquely identify records in the table, Jet can filter by these field values when you use an NF function to get the record.
If you specify a field cache (multiple fields) then Jet will only get the unique combinations of the fields you specified (hence the SQL DISTINCT). Because it's only the unique combinations of those fields, you're not guaranteed to get every record, just every combination of the selected fields. I'm not sure why in Progress OpenEdge this would be affecting the filtering range. Theoretically, I don't think that should be affected; it's certainly not in other databases such as SQL Server.
Anyway, whatever the case, I'm glad you figured out the problem and got it fixed.
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Thanks for the clarification.
Hopefully that won't affect me. The tables that would be affected probably don't have job-id set as unique, as there are multiple rows with the same job-id.
I will run the report for our user, and they can confirm they are happy with the returned data. I can then tinker if there are issues.
Thanks again for your help.
Eds
Please sign in to leave a comment.