calculation problem

[nickOS]

I have a problem with my related db. The parent db has a number of records with different customers, in the child db ( I will call it deliveries and inventory ) where when a delivery takes place, it deducts the delivered amount from the customes inventory and showing the remaining balance.

The two db are related with a serial number ( I will call it Line Item No.)

My problem is that when I have multiple deliveries from a specific customer's inventory, it always deducts from the original amount and not from the balance of the previous delivery.

How can I do this ?/

Nick

[CyborgSam]

I'm guessing here, but it sounds like your relationship is not one-to-one, so the deduction gets applied to the first matching record in the found set (original?).

If I'm wrong, please give us more detailed info, including what tables are in the databases.

[nickOS]

you are correct on your guess.I still cant figure out how to get the child db to deduct from the balance instead of the original amount. Will it help if I attach a stripped down version ??

Nick

[CyborgSam]

Nick->

Yes, attach a version to show us, we can mod it and give it back. I've been too busy and haven't gotten my daily dose of FMForums for way too long...

[nickOS]

Thanks,here it is.

Nick

SimpleInv.zip

[SlimJim]

I think the problem may lay in the definition of RunBalance which is defined to deduct the customers delivery from the original customer amount and not the running balance. A possible solution is to deduct the sum of all the related deliveries. I can't tell if this works as I don't understand the structure well enough.

[nickOS]

Hi gaudeamus,

I have tried to deduct the sum of deliveries from the original amount but it sums up all the deliveries from all the records. The purpose of the RunBalance in the parent is because the child could not see or do the calculation at all. By doing so I got somewhat closer but it will not give me an accurate balance. it has been a Very frustrating experience.

Nick

[SlimJim]

I am making a few assumptions here.

1. DelivOrderNo can be taken as a unique ID for each Deliv record and that these are in strictly increasing order. (I know it is an auto-entered serial but you have allowed for the user to alter it)

2. The Balance that you want to see is the current Balance at the beginning of assigning amounts for a delivery - so that you know how much is available of each customers inventory.

I think it is necessary to make a self-join on the Deliv table in order to achieve a current Balance in that table. I have removed the RunBalance from the Inb tables and redefined it in the Deliv table. I "think" it is working OK but as before you need to look at it to check that. The altered files are attached.

SimpleInv.zip

[nickOS]

I tested it and it works just fine. I really appreciate your help.

Thanks

Nick