Table Column Name to show Location code

hi guys,

as we have a different geographical locations (by countries) end user. i am creating a report to update an item history (eg: purchase, sales,availability qty) by location.

eg (Item Balance By Location)

  1. Item No.
  2. Item Brand
  3. Item Part No.
  4. Total Purchase- Location1
  5. Total Sales- Location1
    .
    .
    .
    6.Total Purchase- Location X

Challenges:
1.Number of field Total Purchase should be ‘dynamically’ changes with number of location.
2.Caption of field should reflect on location code
eg:


Item No. | Total Purchase-Bangkok | Total Purchase-Chiang Mai | Total Purchase-Pattaya
A001 | 10 | 7 |34

I tried not to modified codeunit 1, so i used a function to return code of location
so in my table field
total purchase-location1, caption class i place: ‘Total Purchase -’ + GetName(1)
total purchase-location2, caption class i place: ‘Total Purchase -’ + GetName(2)

Result? When I run my table… the field caption is missing?! :o

GetName(LocationNumber)Text:30
Location.SetRange(Code);
Location.Next(LocationNumber);
Exit(Location.Code);

Hi,

forgive my dumbness… what are you trying to do? What are you trying to “update”? You are talking about a report, but then you mention a table. Which table? What codeunit 1 has to do whith all of this? What is the code at the bottom of your post supposed to do?

I’m confused

hi anna,

thanks for replying. i am running a report to insert quantities by item by locations into the table - Item History. the table will show purchasing, sales history current availibility by location.

but because we had users from different subsidary/ country the location code and number of warehouse location varies.

thus my table field caption would like to display by location code, BNK (Bangkok abbreviation), PHK(Phuket abbreaviations) instead of location1, location2

Hi Nikeman,

if I am getting your problem right, then this approach should work:

  • Create a report with DataItem Item

  • Create an array of type code (10), fill it with the Location Codes by running through the Location table; e.g. like so:
    if recLocation.findset then repeat
    intLocCounter += 1;
    codLocArr[intLocCounter] := recLocation.code;
    until recLocation.next = 0;

    This will result in the assignment of each Location to one array element (e.g. BKK|NRT|INC|HKG|…)

  • Create another array to hold your sales data, e.g. intSalesDataArray of type Decimal

  • Now run through the report (DataItem = Item), do this for each Item:
    Item - AfterGetRecord
    intLocNo := 0;
    if recLocation.findset then repeat //IMPORTANT: use the exact same
    // filters and sorting in the
    // Location table as you had
    // when filling the location
    // array

    intLocNo +=1; // the value of this integer
    //corresponds to the position
    //in the location array

    intSalesDataArray[intLocNo] := //assign values
    GetSalesData(Item.“No.”, recLocation.code); // here, e.g. by a
    // user-defined
    // function. The
    // index intLocNo
    // will correspond
    // to the position
    // in the location
    // array
    until recLocation.next = 0;

This way, each location is numbered, and its number will correspond to one element of the sales data array with the same number.

If there is no data, the text field will simply remain blank on the report output.

This should give you a hint how you cold solve your problem. Note though that the code provided above is not verified, as i have no NAV client available.

hi goelu,

thanks for replying… i did assigned all location code into array… and from the location code extract out individual sales history from item ledger entry. however i miss the column part. and all this data i will be inserting them into a temp table, then a form is created to display all this information in list format to user. the issues i fail to explain properly is to show location code in the form column (field caption) instead of report or table, as from soem of the feedback was that we wouldnt be able to amend/ change table field caption without modifying codeunit 1. thus i can only change the column name(field caption) of form at the run time.

thanks for writing the code too… [:D]