Stock calculations problem

[Marcel Haan]

Hi,

Please have a look at my brain braking problem.

I'm building a system with the use of some new FM7 features which is using a single database for all stock changes.

The records contains a field for sold qty and one for bought qty.

Since every time something is bought it gets a new warehouse location, the idea is to create some relations and calculations which calculate if the stock in that record is still available.

Here's what it looks like now (simplified):

Table : stockmutations

Records:

Recnr. (calc) Get (RecordNumber)

Partsnr. (txt)

Qty bought (num)

Qty sold (num)

Location (txt)

stockchange (calc) qty bought -qty sold

I created a relation "Rel_Stock" from Table stockmutations to itself :

Partnr. = Partnr.

and a relation "Rel_Stock past" from Table stockmutations to itself :

Partnr. = Partnr.

Recnr. >= Recnr.

I added a field :

Available (calc) if (sum(Rel_Stock past::Qty bought)<(sum(Rel_Stock::Qty sold);"no";"yes")

This creates a no of the total qty bought in the previous records is smaller than what is sold overall.

I simplified above a bit since things as ordered parts, reservations etc are also included in what I made so far, but do not change my basic problem.

The value available is in my system also only set if it is a "bought"" record.

Now the problem.

It is possible that there is some stock at location A and location B so what I want to show in an other tables layout is a portal which shows only the records which have the value available "yes"

When I create a relation to table stockmutations with partnr = partnr and avalable = yes, nothing shows up in the portal.

I ran in to the limitation that a portal wont work when a relation is based in a relation.

Does anyone have an idea how to create something which does show only the available 'yes' records with the calculation like above.

I know it is possible with scripts which replaces the continues calculation but that could give a problem with multiuser situations and I just like to try it someway like this.

Your ideas are very welcome.

Marcel

[-Queue-]

If 'yes' is a global field in the main file containing the value "yes", then you can relate it to the available field. If 'yes' is a text or calculation field containing "yes", then your relationship can be accessed both ways.

Does this help?