Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Inventory Deduction Question


72_mach
 Share

Recommended Posts

Hi,

 

I am a new user to Filmaker Pro 8.5 Advanced. Here's my question, I am making a database program for a small store. I have one database called Orders and another called Inventory. When I place an order how do i make FMP automatically deduct the item from the inventory based on the quantity in the Orders database? Also, in the Orders database, their can be more than one item being bought. How do i make a relation between the quanity bought (in Orders), and the quanity in stock (in Inventory)?

 

Thank you in advance

Link to comment
Share on other sites

If you have different items sold in an order I strongly recommend to make a tird table called 'Orders_Contents'

 

This should be related to 'Orders' by the OrderID which should be unique in 'Orders' and should be a foreign key in 'Orders_Contents'

 

'Orders_Contents' should contain information regarding a specific invenotry item, like the ID, quantity, price, etc.

 

This way you can relate 'Orders_Contents' and 'Inentory' with and InventoryID field which should be unique in 'Invenotry' and a foreign key in 'Orders_Contents'

 

When you want to deduct the inventory just loop though the records and

 

Set Field ( Invenotry::InStockQTY ; Invenotry::InStockQTY - Order_Contents::QuantityOrdered )

 

-----------------

**If you don't create a third table it will be hard to have an order with different items unless you use repeating fields, and trut me, YOU DON't WANT TO GO THERE

Link to comment
Share on other sites

Hi,

 

Thanks for replying, if i make a third table, where should i put it? In orders or inventory? And, can you please show me a calculation based on the quantities?

 

if i put in a third table, how do i make it "invisible" so it will look seamless when it deducts on its own?

 

THank you in advance

Link to comment
Share on other sites

Thanks for replying, if i make a third table, where should i put it? In orders or inventory?

 

You are wlecome, well.. you shouldn't confuse a table witha field. One filemaker file can have different tables whcih you create. Each table is made up of fields, like OrderID, DateCreated, InvoiceTOTAL etc...

 

 

And, can you please show me a calculation based on the quantities?

 

It should look like this

Set Field ( Invenotry::InStockQTY ; Invenotry::InStockQTY - Order_Contents::QuantityOrdered )

 

 

if i put in a third table, how do i make it "invisible" so it will look seamless when it deducts on its own?

 

I don't really understand what you are saying, but I created a small sample file with the structure you need. You will need of course more stuff, but it shows how to relate the three tables. You only need a portal to show 'Orders'Contents', the user doesn't need to navigate to that layout. You only need it to loop through the reocrds.

Link to comment
Share on other sites

HI again,

 

Thanks for the help. I would like to clarify a few things. Uhm, for those three layouts (orders, ordercontent and Inventory). ARe those separate FMP files or are they tables all in one FMP file? Could you email me that sample file please? i am having trouble downloading it from this website.

 

ANother question I have is how can i make a script to tell the Inventory to update the quantity only when the invoice has already been printed? is there a trigger i could use to start that action?

 

Thank you so much for the help

 

Email: [email protected]

Link to comment
Share on other sites

Well, first you need to distinguish between a layout and a table.

 

A table is the srtucture of your database, while a layout only displays data, and it can show data from different tables if you set up relationships.

 

So your layouts could be in the same file, and have the tables in different files if you want, but is not a rule...

 

for the script, I would recommend you to make a field called Order Status. Let's say it could be set to 'New', 'Approved' and 'Completed'

 

Make the script that changes the status from 'New' to 'Approved' and deducts inventory at the same time. That way you'll know which ones have already been deducted form inventory. This way you can print an invoice at any time you want.

 

Another method which I use is to create another table that records changes made to inventory so you could go back in case something wrong happens, but that is a little bit more advanced and depends on what you are doing.

 

I sen't you an email, by the way.. I don't recommend you to write your email in public forums because some people make programs that read them and they'll Spam you. you should write it like 'u s e r AT h o t m a i l D O T c o m' and most of us will ge it. Or just send a personal message.

Link to comment
Share on other sites

Hello,

 

Thanks again for the effort, i really appreciate the file you sent me. Anyway, i would just like to ask about that order table on the ORders layout. IS that a portal? does it get the data from both the inventory and the Orders_Status?

 

Right now, i am using Repeating fields for my Orders layout not a portal like yours, is there a way to make repeating fields work with your setup?

 

thanks

Link to comment
Share on other sites

I don't recommend you to use repeating fields. Check out this thread, there are very good explanations on why you shouldn't use them.

http://filemakertoday.com/com/showthread.php?t=10731&highlight=repetition+fields

 

I will be a pain in the butt to do it with repeating fields.

 

If you use a portal, as long as you set up your relationships correctly you will be able to isplay the data. Search threads on the basics of relational deisgn. It will help out a lot for what you need.

Link to comment
Share on other sites

So basically, I should get rid of my repetition fields and use portals instead? By the way, what are multikeys? should i use them instead of repertition fields?

 

Thanks

Link to comment
Share on other sites

AHunter3 gave a very good explanation in another thread..

 

http://filemakertoday.com/com/showthread.php?t=10649

 

Multikeys!

 

(And now I really do feel STOOPID). I woke up this morning with a sense of how you had been using repeating fields. You were using them in lieu of multikey relationships.

 

A multikey relationship is where you have a text field on at least one side of a relationship, and it contains multiple values separated by a hard return, like this:

 

Value 1

Value 2

 

That field will match a record on the other side of the relationship containing either Value 1 or Value 2. Or, looking at it from the other table's vantage point, a record containing Value 1 would match that record, and so would a different record containing Value 2.

 

That, in combinaton with an AND relationship tying your two fields (Supplements and Diseases) in Supplement_Disease to your two fields (Supplements and Diseases) in Studies, should do the same thing you were doing with repeating fields.

Link to comment
Share on other sites

Hi,

 

I have another question, when i create a portal to show the contents of Orders_Status, how come in the Orders layout, i can't see any fields in the portal for me to enter data, unlike the example you showed me in the file? I am sure it's something obvious i am missing but i've been working on it all day and i can't fix it.

 

Thanks you

Link to comment
Share on other sites

Hi,

 

thanks for the reply, here's what i have in mind. The portal will have a field, i put in a part number, the portal will show Description, QTY, QTY avialable, etc. much like the demo you emailed me. How would i make it work like that? without having to input the info in another layout?

 

thanks

Link to comment
Share on other sites

I don't know what you mean by a 'part number'...

You need to create a table called Order Contents and add those fields there (Descirption, Qty, Qty Avaialable etc...)

 

Then in layout mode, add a portal, then add fields.. when you are selecting which field to add, you can specify from what table you are getting the data.

 

Don't worry about the other layout.

 

Maybe I didn't understood what you are trying to ask,

Link to comment
Share on other sites

hi,

 

Sorry about that, i meant Item number. Anyway, let me make clear what i am trying to do. I have one database full of inventory information (item#, price#, quanitity, description,etc)

I have another database called Orders where the user makes orders.

 

I am thinking of letting the user input the item number, then the other fields (price, Quantity, description) fill out by themselves, then at the same time, do what your example does, subtract the items bought.

 

Right now i created the ORders_status table but i don't want to input the invenotry information there, is that possible?

 

I checked out yours and it seems that that table is the main database. Is there a way i only have one table for the inventory and still make all those things work?

 

thank you so much for your time and help

Link to comment
Share on other sites

Yes, actully in the example, the invenotry is a separate table. (also, I don't think you need a status table, unless there can be different status for one order at the same time, but I don't think thats the case. I would recommend yu to make it a field in the Orders Table)

 

I just put an instance of that table related to OrderCOnents by InventoryID

 

You can either have global fields than the user inputs, then have a script that creates the new record in the contents table filling out the required fields with what the user put in.

 

Or, in the relationship s graphs, you click on a relationship, and in the detail you can select "Allow creation of records through this relationship"

 

That will make the fields in the portal editable, and you can add records directly on the portal, You need to set upsome fields like "description" " "price" to Auto-enter a Lookedup Value. That way when a user inputs the inventoryID it will copy the description and price automatically to your order contents table.

 

Am I going to fast? Is this what you need?

Link to comment
Share on other sites

Hi,

 

Well, the problem i am having now is making the portals work. I create a portal using Layout mode but when i go to browse it shows me a blank looking table. Is it okay if i email you what i have so far so you could advise me on the best way to go?

 

thanks again

Link to comment
Share on other sites

yeah, send it to me... I layout mode don't just add the portal.. add fields to the portal also. If not nothing will appear. Is just like adding fields inside the layout.

 

Think of it as a layout inside a layout

Link to comment
Share on other sites

Hi,

 

I just received your email and i am happy with what you have done. One more thing, if i want to subtract the inventory, should i add a button like what you had in your example? another question i have is should i make the invoice total a portal or a lookup from the Items_Purchased table? ANother seemingly complex thing is that i have 3 prices for one item (New, used and reproduction replacement). (These fields are on the Form View page of Inventory table).

 

Is there a way to create a drop down selection for each item price? thanks so much in advance.

Link to comment
Share on other sites

Now, one question... In the MainInventory file you have you have a field that is called QTYinstock which indicates the amount of itmes instock (I guess)

 

But you have 3 fields of prices for one item, which is the price if the item is new, used or repo... so, if you have 8 items in stock, how would you differentiate how many are new, how may are used and how many repo?

 

I think this might become a database structure problem, unless an item will always be new, or always used and don't combine them, which in that case you won't need three different fields for the price. You would only need a field to identify if it is new, used or repo.

Link to comment
Share on other sites

I did a shoes invenotry database,

 

Let say I have style A, B and C, all are different shoes,

 

But each shoe might have different sizes ins tock,

Style A has sizes 5-10 in stock, style B has 8-12 mens in stock and C has small, medium and large.

 

this might become a problem, one mistkae (BIG MISTAKE) I did first was to add a bunch of fields to the items DB which holded QTY in stock stock for each size. that was not nice, because to deduct inventory I had to script every size possible.

 

What I ended up doing was to buiild a separate table called Invenotry, whcih holded, and inventoriable ID and the Qtyinstock.

 

So I could view an Item, and in a portal look at the inventoriable options, in this case was size, and the quantity in stock for each one.

 

This is bettern instead of creating one item for every inventoriable option you have, and you could control easier when a product goes out of stock in a specific option.

 

In this case, your options are 'New' 'Used' and 'Repo' and you could add more options if you wanted.

Link to comment
Share on other sites

HI

 

That suggestion seems good but what do you think is the best way to approache the inventory deduction for each "option" of the same item? Is there a way i can just input the part # then there will be a drop box to choose "new, used or repro" then the database will deduct from that corresponding quantity?

 

thanks

Link to comment
Share on other sites

Have you ever done a Value List based on a relation, or a confditional Value List. try doing a search for that topic,

 

You could enter the part# in one field, and in another field select if it's "New Used or Repo" based on a valuelits that displays only what you have in stock.

 

For example, if you have only new stuff instock, you might want to restrit users to select only new, unless you can place in backorder..

 

 

To deduct invenotry, you need to set up your Invenotry Deduction relationship to the new table where you hold the qty in stock, related by both part# and type. (You can do a relationship based on multiple properties)

Link to comment
Share on other sites

HI,

 

Is it better to keep the inventory for each type of item in a separate table? or should i just put it into the Items_purchased table? ALso for the scripts, should i do it as a calculation field, or just using scriptmaker?

 

thanks

Link to comment
Share on other sites

Yes, I would keep the invenotry in a separate table.

 

A scrpt can only be done in Scriptmaker, I don't know how you would use a calculated field, in a calculated field you can diasplay data, but you can not change the amount in stock. You need a script that sets the field

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use