vi job export - custom fields - group by

Posted By Daniel Ahern over 1 year ago

1) For inventory, I'm trying to get the sum total of all inventory in warehouses where warehouse number in (100,200,300). Additionally, my formula for getting the inventory is:
IM_ItemWarehouse.QuantityOnHand -IM_ItemWarehouse.QuantityOnPurchaseOrder -IM_ItemWarehouse.QuantityOnSalesOrder -IM_ItemWarehouse.QuantityInShipping

So, I'm wondering how to get a field that is a formula of other fields, but only where the inventory is in certain warehouses.

2) Also, I'm wondering how I can choose "Last Cost" in a vi job export.

0 BigLouie over 1 year ago

Have you tried to write a Crystal Report
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Daniel Ahern over 1 year ago in reply to BigLouie

I did, but I wasn't able to export from the command line. I've written what I've done as a reply to my own question.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Daniel Ahern over 1 year ago

I ended up writing a golang script for this, since I needed to modify the columns anyway, and wanted to put the resulting csv onto an ftp server (we are uploading our inventory to Sage 100).

For 2), you can get LastTotalUnitCost from CI_Item.

This is the script I wrote. It'd be pretty easy to modify. (If you need help you can ask me here or exults.com is the company I work for - they can help you as well for $$.) You just need a .env file in the same directory with the creds to your ftp server and to your local ODBC driver:

FTP_SERVER=

FTP_USER=

FTP_PASSWORD=

FTP_PATH=

#ODBC

DSN=

UID=

PWD=

COMPANY=

And this is the script we wrote on golang 1.18 - (I made a separate script for LastPrice):

package main

import (

"database/sql"

"encoding/csv"

"fmt"

"io/ioutil"

"log"

"os"

"strings"

_ "github.com/alexbrainman/odbc"

"github.com/jlaffaye/ftp"

"github.com/joho/godotenv"

)

func main() {

// Load environment variables from .env file

err := godotenv.Load()

if err != nil {

log.Fatalf("Error loading .env file: %v", err)

}

// Set up FTP connection parameters

ftpServer := os.Getenv("FTP_SERVER")

ftpUser := os.Getenv("FTP_USER")

ftpPassword := os.Getenv("FTP_PASSWORD")

ftpPath := os.Getenv("FTP_PATH")

filename := "item_inventory.csv"

// query := `select top 10 ItemCode, LastTotalUnitCost from CI_Item where LastTotalUnitCost <> 0`

// Define the query to run.

query := `

select IM_ItemWarehouse.ItemCode,

sum(IM_ItemWarehouse.QuantityOnHand - IM_ItemWarehouse.QuantityOnPurchaseOrder - IM_ItemWarehouse.QuantityOnSalesOrder - IM_ItemWarehouse.QuantityInShipping) as qtyAvailable,

max(IM_ItemWarehouse.AverageCost) as avgCost

from IM_ItemWarehouse

where (IM_ItemWarehouse.QuantityOnHand - IM_ItemWarehouse.QuantityOnPurchaseOrder - IM_ItemWarehouse.QuantityOnSalesOrder - IM_ItemWarehouse.QuantityInShipping) <> 0

and (IM_ItemWarehouse.WarehouseCode = '100' or IM_ItemWarehouse.WarehouseCode = '200' or IM_ItemWarehouse.WarehouseCode = '300')

group by ItemCode

`

// Define the output CSV file.

outputFile, err := os.Create(filename)

if err != nil {

log.Fatal("Failed to create output file: ", err)

}

defer outputFile.Close()

// Connect to the ODBC data source.

connectionString := fmt.Sprintf("DSN=%s;UID=%s;PWD=%s;COMPANY=%s", os.Getenv("DSN"), os.Getenv("UID"), os.Getenv("PWD"), os.Getenv("COMPANY"))

db, err := sql.Open("odbc", connectionString)

if err != nil {

log.Fatal("Failed to connect to ODBC data source: ", err)

}

defer db.Close()

// Run the query.

rows, err := db.Query(query)

if err != nil {

log.Fatal("Failed to run query: ", err)

}

defer rows.Close()

// Write the query results to the CSV file.

writer := csv.NewWriter(outputFile)

// Write the CSV header.

headers := []string{"ItemCode", "qtyAvailable", "avgCost"}

writer.Write(headers)

// Write each row to the CSV file.

for rows.Next() {

var itemCode string

var qtyAvailable int

var avgCost float64

if err := rows.Scan(&itemCode, &qtyAvailable, &avgCost); err != nil {

log.Fatal("Failed to scan row: ", err)

}

row := []string{itemCode, fmt.Sprintf("%d", qtyAvailable), fmt.Sprintf("%.2f", avgCost)}

if err := writer.Write(row); err != nil {

log.Fatal("Failed to write row to CSV file: ", err)

}

}

writer.Flush()

fmt.Println("CSV file generated successfully")

// Read generated CSV file into memory

csvData, err := ioutil.ReadFile(filename)

if err != nil {

fmt.Printf("Error reading CSV file: %v\n", err)

return

}

// Connect to FTP server

ftpConn, err := ftp.Dial(fmt.Sprintf("%s:%d", ftpServer, 21))

if err != nil {

fmt.Printf("Error connecting to FTP server: %v\n", err)

return

}

defer ftpConn.Quit()

err = ftpConn.Login(ftpUser, ftpPassword)

if err != nil {

fmt.Printf("Error logging in to FTP server: %v\n", err)

return

}

err = ftpConn.ChangeDir(ftpPath)

if err != nil {

fmt.Printf("Error changing FTP directory: %v\n", err)

return

}

// Write CSV file to FTP server

err = ftpConn.Stor(filename, strings.NewReader(string(csvData)))

if err != nil {

fmt.Printf("Error writing CSV file to FTP server: %v\n", err)

return

}

fmt.Println("CSV file sent successfully to FTP server")

}
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 BShockley over 1 year ago

I would create a Crystal Report and export this data to Excel, if an Excel file is what you ultimately need.

Crystal can do the groups and summaries you need. Last cost should be in the CI_Item table.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Kevin M over 1 year ago in reply to Daniel Ahern

Look up a 3rd party utility called Visual Cut. It's a Crystal Reports tool that does command line processing, including FTP / SFTP uploads.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Sage 100

vi job export - custom fields - group by

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING