Hi all

Firstly I will put the problem into words, then I will follow it with what I have done and then I would request you to provide me with suggestion to improve and avoid any pitfalls that maybe due to wrong understanding on my part. I am pretty sure someone or the other would have had to deal with this kind of complications.


Part I - Problem:
I have a series of parent-child table sets. Namely, tQuotation and sQuotationItems, tProformaInv and sProformaInvItems, tPackingList and sPackingListItems.

The Quotation is entered first. Each of these quotations have a serial no. and party in tQuotation and contain multiple items in sQuotationItems. Related to each other by iID in tQuotation and iQID in sQuotationItems. For eg.

iID 1, iQNo 1, iPartyID 5
   iID 1, iQID 1, iItemID 56, nQty 100
   iID 2, iQID 1, iItemID 85, nQty 125

iID 2, iQNo 2, iPartyID 5
   iID 3, iQID 2, iItemID 35, nQty  50
   iID 4, iQID 2, iItemID 85, nQty 225

The Proforma Invoice is created. Each of these pro. inv. have a serial no. and party in tProformaInv and contain multiple items in sProformaInvItems. Related to each other by iID in tProformaInv and iPIID in sProformaInvItems.

At this stage of pro. inv. creation the user will enter the sr. no., party. After which I have created a mover form to get the selection of pending quotation nos. which can be setoff against this pro. inv. Once the nos. (eg. quot. nos. 1,2) are selected I have created enough tables and logic to generate a clubbed list of items and total pending quantities. Thus the following data is generated:

iID 1, iPINo 1, iPartyID 5, cQuotNos 1,2
   iID 1, iPIID 1, iItemID 35, nQty  50
   iID 2, iPIID 1, iItemID 56, nQty 100
   iID 3, iPIID 1, iItemID 85, nQty 350

Things start to get complicated now. The user is to be at the liberty to modify the qty. as required so the same above data can become something like this:

iID 1, iPINo 1, iPartyID 5, cQuotNos 1,2
   iID 1, iPIID 1, iItemID 35, nQty  30
   iID 2, iPIID 1, iItemID 56, nQty  65
   iID 3, iPIID 1, iItemID 85, nQty 175

so at a later date when the user creates another pro. inv. and provide the same party and again selects the quotation nos. 1,2, the following data has to be created:

iID 2, iPINo 2, iPartyID 5, cQuotNos 1,2
   iID 4, iPIID 2, iItemID 35, nQty  20
   iID 5, iPIID 2, iItemID 56, nQty  35
   iID 6, iPIID 2, iItemID 85, nQty 125

and so on between Proforma Invoice and Packing List.


Part I - Current Table Structures (simplified):
tQuotation

iID        Integer   Primary Key
iQNo       Integer   Quotation Sr. No.
iPartyID   Integer   Party's ID in relation to the Party Master table

sQuotationItem

iID        Integer   Primary Key
iQID       Integer   Foreign Key in relation to iID in tQuotation
iItemID    Integer   Item's ID in relation to the Item Master table
nQty       Numeric   Quantity of the item

tProformaInv

iID        Integer   Primary Key
iPINo      Integer   Pro. Inv. Sr. No.
iPartyID   Integer   Party's ID in relation to the Party Master table
cQuotNos   Char      The quot. nos. in a comma seperated list

sProformaInvItem

iID        Integer   Primary Key
iPIID      Integer   Foreign Key in relation to iID in tProformaInv
iItemID    Integer   Item's ID in relation to the Item Master table
nQty       Numeric   Quantity of the item

The following is the adjustment table I have implemented to find out balance qty. as per above eg.
uOSStock

iID        Integer   Primary Key
iPID       Integer   Parent Tables FK is stored here
cPTable    Char      The name of the parent table like SPROFORMAINV, SPACKINGLIST and for WHERE clause purposes
iDID       Integer   Connected tables FK is stored here
cDTable    Char      The name of the connected table like SQUOTAITON, SPROFORMAINV resp. to  cPTable above and for WHERE clause purposes
iItemID    Integer   Item's ID for WHERE clause purposes
iPartyID   Integer   Party's ID for WHERE clause purposes
nQty       Numeric   The adjustment qty.

The above eg. will traslate to the following data in uOSStock table

iID 1, iPID 1, cPTable SPROFORMAINVITEMS, iDID 3, cDTable SQUOTATIONITEMS, iItemID 35, iPartyID 5, nQty 30
iID 2, iPID 2, cPTable SPROFORMAINVITEMS, iDID 1, cDTable SQUOTATIONITEMS, iItemID 56, iPartyID 5, nQty  65
iID 3, iPID 3, cPTable SPROFORMAINVITEMS, iDID 2, cDTable SQUOTATIONITEMS, iItemID 85, iPartyID 5, nQty  175
iID 4, iPID 4, cPTable SPROFORMAINVITEMS, iDID 3, cDTable SQUOTATIONITEMS, iItemID 35, iPartyID 5, nQty  20
iID 5, iPID 5, cPTable SPROFORMAINVITEMS, iDID 1, cDTable SQUOTATIONITEMS, iItemID 56, iPartyID 5, nQty  35
iID 6, iPID 6, cPTable SPROFORMAINVITEMS, iDID 2, cDTable SQUOTATIONITEMS, iItemID 85, iPartyID 5, nQty 125

more data will be added when packing lists are created so to continue the above data:

iID  7, iPID 21, cPTable SPACKINGLISTITEMS, iDID 33, cDTable SPROFORMAINVITEMS, iItemID 35, iPartyID 5, nQty 20
iID  8, iPID 22, cPTable SPACKINGLISTITEMS, iDID 31, cDTable SPROFORMAINVITEMS, iItemID 56, iPartyID 5, nQty  60
iID  9, iPID 23, cPTable SPACKINGLISTITEMS, iDID 32, cDTable SPROFORMAINVITEMS, iItemID 85, iPartyID 5, nQty  185
iID 10, iPID 24, cPTable SPACKINGLISTITEMS, iDID 33, cDTable SPROFORMAINVITEMS, iItemID 35, iPartyID 5, nQty  30
iID 11, iPID 25, cPTable SPACKINGLISTITEMS, iDID 31, cDTable SPROFORMAINVITEMS, iItemID 56, iPartyID 5, nQty  40
iID 12, iPID 26, cPTable SPACKINGLISTITEMS, iDID 32, cDTable SPROFORMAINVITEMS, iItemID 85, iPartyID 5, nQty 115

This is almost, if not all's well, for FIFO.



Part II - Further complications
Now the user wants to adjust the pro. inv. qty. partialy against various quotations, so the above pro. inv. data:

iID 1, iPINo 1, iPartyID 5, cQuotNos 1,2
...
   iID 3, iPIID 1, iItemID 85, nQty 175

will translate to:

uOSStock (I have purposly put in a and b in iID fields for comparing purposes)

...
iID 3a, iPID 3, cPTable SPROFORMAINVITEMS, iDID 2, cDTable SQUOTATIONITEMS, iItemID 85, iPartyID 5, nQty  100
iID 3b, iPID 3, cPTable SPROFORMAINVITEMS, iDID 4, cDTable SQUOTATIONITEMS, iItemID 85, iPartyID 5, nQty   75
...

Part II - Problem
Now the problem arises when the quot. nos. are inputed and store under tProformaInv. And the item qty. data created is clubbed (they *want* the qty. clubbed). And yet this clubbed qty. is to be assigned in seperate nos. to different quotations. What kind of an interface can I create when the qtytextbox.lostfocus() is execute? What kind of fields are to be added to sProformaInv to store the splitted qty.? The split of qty. is not fixed to 2 quot. it can be fully to one quot. or more then 2 quot.



My Request
Please provide me with suggestion for improving Part I, which I have already developed. As it is still in the development environment I am open to it's improvement. I have no idea what pitfalls are out there with the above idea in practical implementations and in the hands of the user.

Please provide me with ideas for implementing Part II. If it means modifying Part I no problems. I have come to a stage where I am pretty run out of ideas.