How to calculate remaining stock levels

[Madwolfie]

Hi

It is a long time since I have posted, but am in need of help.

I have a built a database which I use to calculate the cost of a picture frame. This is split with separate tables of 'Calculator', 'Moulding', 'Mountboard' etc. The calculator works out the cost by various calculations based on the length and width of the frame and cost of the moulding etc.

What I am trying to do is to input an amount for the starting stock of moulding length, then I would like each record in the 'Calculator table' - (a sold picture which has been framed) to subtract from the stock level the length of moulding used.

I cannot work out which table to put this on or how to go about the calculation - I tried a portal which just gives me the value of moulding used, this needs to be dynamic and 'live' using all the records (framed pictures done) and adding them all up then taking them away from the original stock level. But of course there are many different mouldings so it needs to work and display the results for a specific moulding ID (which exists) - not all the mouldings lumped together.

Any help would be appreciated

Cheers

C

[Søren Dyhr]

This is split with separate tables of 'Calculator', 'Moulding', 'Mountboard' etc.

Why are these chosen? When usually it's a recursive relational structure consisting of at least the following tables:

Assembly

Product

Production

Recieving

...allright I might spot a synonymous resemblance here and there, but what is the difference between molding and mountboard?

--sd

[Madwolfie]

Hi

Thanks for the reply, but I am now confused ! The database is basically a calculator for a price to frame a picture, based on Frame choice (picture moulding type), Mountboard choice (mat), Glass and a few other components. At the moment I have tried to do this with relationships with separate tables within one database.

But, I am not very good at relationships. What would your advice be, to enable me to build the best starting platform.

thanks in advance

C

[Søren Dyhr]

The database is basically a calculator for a price to frame a picture,

Yes but do you have a stock of assemblies or sets? Or are e every thing made ad hoc?

If it's the later is the case, could it probably be dealt with as individual lines as in an invoice, where each individual part occupies it's own line. But to include safeguards against daft combinations doesn't exist at all.

If each component has an ID would it be possible to see if attempts to pull more than the stocklevel, after the line is commited by colorcoding the line.

Your tool doesn't without severe shenanigans allow you to see those stock levels ahead of the choice, in order to let you choose an alternative which is possible ... as far as I remember.

We're now on the threshold of when version 9 expires. I have long time ago ditched my mac os 9 and fm5, so I can't really make you any template showing you how to, unless I try something in this direction:

http://uneasysilence.com/archive/2006/08/7352/

...which by the look of it isn't fully legal, so I would strongly suggest you get a more contemporary version.

--sd

[Madwolfie]

Hi

Thanks for the reply - again even more confused, I am running FM 8.5 on Leopard on a Xeon Quad core MacPro.

Relationships seems to be where I am falling down, each record in the Calculator table is a price quotation/Worksheet for a picture that is to be framed. As such it performs calculations using data from other tables (Glass, Frame, Mount etc etc) so a stock level of Framing material (for example), would have to be calculated from the frame lengths used in each picture (SUM) taken away from the stock level in the Moulding(Frames) table.

Cheers

C

[Søren Dyhr]

the structure of data doesn't have to match the presentation

Sorry to confuse you, but your way of approaching relational concepts, might lack abstract thinking? ...or rather too tied to spreadsheet'ish approaches of linking. The making of distinctive tables for each kind of material, is a relational flawed approach - they need to go into the one and same table!

http://www.filemakermagazine.com/videos/data-tagging-classification-vs-organization.html

--sd

[Colin Keefe]

Soren's dead right that separating out your materials into separate tables is a bad idea.

Maybe something more along these lines would be more sensible?

InventoryLevels.fp7.zip

[Madwolfie]

Hi

Hmm, I have read and listened to the video etc - the Inventory file looks to me quite similar to my own file, but I am still finding it hard to understand how the main table (in my case called 'Calculator') can hold all the information. As the table for Frame mouldings has 62 different types of frame with prices widths and pictures of the frames, the Mountboard table has similar numbers of different records of different colours of mount.

The 'Calculator' is basically the main part where prices for the finished frame are worked out from the individual components, they are all simply connected by relationships on the Unique ID numbers for each item.

Maybe I should send the file - but it is a bit large as it contains pictures of the frames.

I have managed to get the stock calculation to work with scripts, but it is a bit clumsy ! I would love to learn how to design a better database.

Cheers

C

[Søren Dyhr]

As the table for Frame mouldings has 62 different types of frame with prices widths and pictures of the frames, the Mountboard table has similar numbers of different records of different colours of mount.

Try to exhaust ALL the links shown here:

http://fmforums.com/forum/showpost.php?post/283672/

Maybe I should send the file - but it is a bit large as it contains pictures of the frames.

Not the best of ideas, uploads here should only show principles used, not lack of them - with a sticker attached "fix this mess please"

You need to explain in plain words about your relational approaches instead of letting us attampt to digest, what should be the idea behind.

--sd

[comment]

Maybe I should send the file - but it is a bit large as it contains pictures of the frames.

Why don't you make a clone of it, enter a sample record or two, and post that.

[Madwolfie]

Hi

I am sorry, but I thought I had done that - at least I tried to.

C