Jump to content

Stock calculations problem

This topic is 7096 days old. Please don't post here. Open a new topic instead.

Recommended Posts


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


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.


Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This topic is 7096 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.