Display X-Reference No. for given Customer & Item on Rep

johnsogj

Hello, I'm building a report that is based on the item table. I'd like to go look in the Item Cross Ref. table and display the Cross-Reference Number for a given customer/ item combination. Does anyone have any tips? I've tried a bunch of combinations of SETCURRENTKEY and SETRANGE - nothing seems to be working. (I dont get an error, it just doesnt return any values in that column on the report)

here is one example of what I've tried:

Xref.SETCURRENTKEY("Item No.","Cross-Reference No.");
Xref.SETRANGE("Item No.",Item."No.");
Xref.SETRANGE("Cross-Reference Type No.", 'C00010','C00010');
XNo := Xref."Cross-Reference No.";

Savatage

Not sure I'm following...
If I use the Item Cross Reference Table as the base of my report.

When I run the report the dataitem filters you add are
Item No. -> will let you filter what item
Item Cross Reference Type = Customer
Item Cross Referecne type No. will give you your customer.

Why do you need to do it in code?
do you want to run it from the customer's card?
Is there any time you want to filter out some of the item no's?
Are you trying to add Item Table information to this report?

johnsogj

I'm running it from the item table because the majority of the info I need to pull is in the item table. Also, there are 4 different customers for which I need to display their cross-ref. item number. each will be in separate columns.

Savatage

is it only 4 customers forever or do you want to possibly expand it out to more as time goes by?

We do this for 3 Main customers and for ease purposes I created 3 Flowfields in the item table that lookup the cross reference by item by customer. so all your info would be in the item table.

Note: some speed issues might be a factor if you have large amounts of items and/or cross references, but I have not been affected by this in our system.

Also it's quick & easy to do it this way, but like I said if in the future if you want to expand it out to a dozen or more customers then I wouldn't do it this way.

To keep things organized you could creat a new tab to hold just these fields for quick viewing of these special circumstance numbers.

if this doesn't work for you then well try the next step.

johnsogj

The plan is definitely to expand this to many more customers. Also - we have thousands of items and cross-reference. (not to mention, I dont know how to set up flow fields yet either!)

do you think this can be done just through code?

thanks again for your time

Savatage

johnsogj wrote:

I'm running it from the item table because the majority of the info I need to pull is in the item table. Also, there are 4 different customers for which I need to display their cross-ref. item number. each will be in separate columns.

Here's lies one of the problems you need to address. If you plan on expanding this out then I don't think you can have them in seperate columns like

Item#abc Cust1#123 Cust2#234 Cust3#345 Cust4#456
You probably have to look make it look like

->All your Item Information
-->All the Cross References associated with this item

Item ABC
-Cust1#123
-Cust2#234
-Cust3#345
-Cust4#456

*especially if your serious about expanding this from 4 customer to many.?

Might look cool in a matrix form 8)

johnsogj

we really want to have it in separate colums because we will export it to excel. (and that way we will never run out of room even if we run it for a bunch of customers.) you dont think it is possible to display it in columns so all info for 1 item is in 1 row? even if the specific customers are hard coded?

Savatage

My first thought was something like this:
http://www.mibuso.com/howtoinfo.asp?FileID=13
matrix form. I haven't tested it but perhaps onec this is done you can simply copy & paste it into excel.
:-k

garak

when you will print it out in Coloums, you cann fil an Temp table and get them out in an integer Data Item. But with this soloution, you can only print out an def. amount of coloums :-(

Why not print Lines?

To Display it on the screen, u can use an matrix form.

When you will transfer the datas to excel, theres also no problem, see excel buffer (or own solution).

fast solution with report and line printing

1. Dataitem -> Item
2. dataitem -> Item Cross Reference <- linked to Dataitem Item and filters of Type. Customer No. can be filtered by user

johnsogj

That matrix looks cool. I will definitely read about that - but it looks complicated!! do you think it will allow me to tack on the item card for each item as well?

but until I figure this matrix out, do you know if it can be done with just the more simpler, SETCURRENTKEY, SETRANGE standard coding to look up the customer specific data in the Cross Reference table based on the current item being written?