Hi,

I'm trying to select records from PriceLevelPerItem for just one item.

We have 5 price levels for this item.

I am using the following query:

SELECT ListID, Name, PriceLevelPerItemItemRefListID, PriceLevelPerItemCustomPrice

FROM PriceLevelPerItem NOSYNC"

WHERE PriceLevelPerItemItemRefListID = ' < ListID of item > '

However, Only one price level is returned for this item, not the 5 (five) I expected.  It happens to be the first price level for this item.  This item has 5 custom prices in QB when I look into the price list there.

Any ideas?

Thanks,

You're using the NOSYNC tag here, so the QODBC optimizer won't update the PriceLevelPerItem table. I did:

SELECT ListID, Name, PriceLevelPerItemItemRefListID, PriceLevelPerItemCustomPrice
FROM PriceLevelPerItem NOSYNC
WHERE PriceLevelPerItemItemRefListID = '20000-933272655'

and it showed all three lines for me. Try resyncing your PriceLevelPerItem table by doing:

sp_optimizefullsync PriceLevelPerItem

Hi,

That worked great!  It discovered all our 65,000 records.  (Not the original 13,000).

However, now that I was able to completely populate a SQL Server table, and update prices there, I am now trying to send an UPDATE statement back to QB via QODBC for each item and price level.

When I send the first UPDATE statement, QODBC goes back to counting up the original 13,000 items.  It's like the re-sync never ran.  It forgot there were 65000 records.

The statement fails, presumably because the first item is not found in whatever "temporary" table that QODBC maintains.

(1.  Is there a way to make the re-sync "stick" ?)

We can't run an 8-hour re-sync every day.

Moreover, QODBC wants to loop through ALL the "temporary" table records FOR EACH update statement.

(2.  Is there a way to avoid this ?)

For 500 items and 5 price levels, that's 2500 repeats of looping through the table.  Obviously, at 15 minutes per loop, that would take quite a long time.)

Thanks,

Victor

To update a PriceLevelPerItem we used to do this:

UPDATE PriceLevelPeritem SET PriceLevelPerItemCustomPrice = 43.21, PriceLevelPerItemItemRefListID = 'F0000-933272656'  WHERE ListID = '30000-1197755980' AND PriceLevelPerItemItemRefListID = 'F0000-933272656'