Technical and Installation Discussions

[JoeMcFadden]

I looked through the resources tab, and even fired up Crystal Reports, and spent an hour searching for what I need.

I was able to make a connection, and get data, but not the data I need.  (I've setup the DSN, and it works fine.)

I am writing a custom ordering system for a client who uses MAS90, and I need to be able to update a mySQL database, since the client understandably would rather not manually update their prices 2 times (thousands with multiple prices for each)

Here's what I am trying to do:

Pull an array containing: Item name, UPC, Price_A, Price_B, Price_C, Price_D, Price_E, Price_F, Price_G, Price_H

Where can I find this data in the database? Everything I see online points to a table called: IM1_MasterInventory or similar,  which I do not see at all. I see many tables beginning with IM_  My client is using MAS90 Version 4.4.

Thanks!

[Natasha · Accepted solution]

If you want the prices by qty breaks, IM_PriceCode is the file you want.  Depending on the pricing method, you may need to do markup or discount calculation to come up with the price.  CI_Item has standard price and suggested retail price.  It also has cost info that you may need to calculate prices.

[JoeMcFadden]

thank you for the response, I will take a look at those tonight.

[JoeMcFadden]

Thank you! Using CI_Item, I am now able to get the following information from the MAS90 DB:

'upc'   (ItemCode) 
'description' (ItemCodeDesc)
'brand' (ProductLine) 
'price' (SuggestedRetailPrice)
'stock' (TotalQuantityOnHand)

What I still can't find:

In MAS90, modules, inventory Management, Main, Item Maintenance;  after making an Item Code selection, if you click on More, Pricing; under Price level, my client has A ~ H, all saying Price Method: Price Override. This is where they keep 8 different prices for every item. 

Where can I find this information in the database?

[Heather]

These are in the IM_PriceCode file that Natasha mentioned. 

Because you say they are all overrides, you should have a relatively easy time extracting them. 

However, you should still be aware that the pricing method doesn't have to be an override -- there are other options, like percent off and cost markup.  You might want to familiarize yourself with the options and plan for them in case your client changes how they do things.

[JoeMcFadden]

Ok, I was able to find everything, thank you both for your help!

Here is where I found the data, along with my PHP code in case someone comes here for the same reason:

<?

$db=odbc_connect("DSN=MAS90PHP;","","");

$x=0;$sql="SELECT * FROM CI_Item";$query=odbc_exec($db, $sql);

while(odbc_fetch_row($query))
{
 $temp_upc[$x] = trim(odbc_result($query, "ItemCode")); 
 $temp_description[$x] = trim(odbc_result($query, "ItemCodeDesc"));
 $temp_brand[$x] = trim(odbc_result($query, "ProductLine"));
 $temp_price[$x] = trim(odbc_result($query, "SuggestedRetailPrice"));
 $temp_stock[$x] = trim(odbc_result($query, "TotalQuantityOnHand"));
 $temp_promo[$x] = trim(odbc_result($query, "SalesPromotionCode")); $x++;
}

$x=0;

$last_upc = '';
$sql = "SELECT ItemCode, CustomerPriceLevel, DiscountMarkup1 FROM IM_PriceCode";

$query = odbc_exec($db, $sql);

while(odbc_fetch_row($query))
{
 $temp_itemcode = trim(odbc_result($query, "ItemCode"));

 if (($last_upc)&&($last_upc <> $temp_itemcode)){$x++;}

 $temp_upc_letter[$x] = $temp_itemcode;
 $temp_letter = trim(odbc_result($query, "CustomerPriceLevel"));
 $temp_letter_val = number_format(trim(odbc_result($query, "DiscountMarkup1")),2);

 if ($temp_letter == 'A'){$temp_letter_A[$x] = $temp_letter_val;}
 if ($temp_letter == 'B'){$temp_letter_B[$x] = $temp_letter_val;}
 if ($temp_letter == 'C'){$temp_letter_C[$x] = $temp_letter_val;}
 if ($temp_letter == 'D'){$temp_letter_D[$x] = $temp_letter_val;}
 if ($temp_letter == 'E'){$temp_letter_E[$x] = $temp_letter_val;}
 if ($temp_letter == 'F'){$temp_letter_F[$x] = $temp_letter_val;}
 if ($temp_letter == 'G'){$temp_letter_G[$x] = $temp_letter_val;}
 if ($temp_letter == 'H'){$temp_letter_H[$x] = $temp_letter_val;} 

 $last_upc = $temp_upc_letter[$x];
}

?>