FINDSET of Table that is SQL View won't find records

VoltaixIT

I have a table (VOL_HAZOX_INFO) that is a SQL View. I can run the table from the designer and see all the fields and records. Results look like this:

ItemNo HazField HazValue
200377 HC 2.1
200377 IMDG_CODE 2124
200377 NAERG 115
200377 SN COMPRESSED GAS, FLAMMABLE, N.O.S.
200377 UN# UN1954

When I try to read the data from code in a report, I get an error "VOL_HAZOX_INFO ItemNo '200377' does not exist."
I am new to this, so I imagine I am doing something simple that is wrong.

The code is:

Hazdata.SETCURRENTKEY(ItemNo);
Hazdata.SETRANGE(ItemNo, "Sales Shipment Line"."No."); ==> this will be 200377
Hazdata.FINDSET;
REPEAT
Hazards := Hazards + Hazdata.HazField;
UNTIL Hazdata.NEXT = 0;

Hazdata is a C/A Global type rec assigned to the VOL_HAZOX_INFO source.

Thanks for any help you can provide.

VoltaixIT

I changed the SQL query to convert the ItemNo field from NVARCHAR to VARCHAR to match the No. Field in the Sales Shipment Line. The error is now gone, however I seem to only be getting 1 record back from the FINDSET. Hazards = "HC 2.1"

bbrown

What is the primary key of the table linked to "VOL_HAZOX_INFO" and does it identify a unique record?

VoltaixIT

I got past the error by changing the SQL view to convert ItemNo from NVARCHAR to VARCHAR. The FINDSET now finds the records and a Count on the data shows there are 5 records (which is correct).
However, I can't seem to step through the records. The REPEAT..UNTIL code only returns 1 record and the Counter I added stays at 1.

Counter := 0;
REPEAT
AllHazards := AllHazards + Hazdata.HazField + ' ' + Hazdata.HazValue ;
Counter := Counter + 1;
UNTIL Hazdata.NEXT = 0;

The HazData SQL View does not return any unique IDs.

Thanks

bbrown

NAV requires its tables to have uniquely identifiable records. As such, any SQL views that will be linked to NAV tables must follow this same rule.

VoltaixIT

I concatenated the first 2 fields in the View to create a unique key - all's well, thanks.