Forum post
hello there,
Sorry if this is a stupid question, but is there any way that I can create calculations that access more than one table? What I am trying to do is to set up an inventory system for my little business creating and selling natural products. So I need a manufacturing (or materials?) space which feeds into my items library. I also want to track what new materials I purchase so that this updates my inventory numbers, and to have sales orders which deduct from my inventory.
I figured that I would need to have a calculation that could cross reference tabnles/libraries in order to add/subract but having looked through and played around with it a bit, I haven't been able to see how to do it. My free trial runs out soon and I want to figure out if this is something that could work for me before I buy. I love the concept of it so really WANT it to work...
thanks!
Lucy
Answer
Lucy Ashman:
Thank you for your post.
A Related Data field type allows you to link records from another Library to a record in the current Library. For example, in your Sales Order Library, you would have a Related Data field into your Inventory Library so you can link records from the Inventory Library to a particular Sales Order record. An easy way to create a Related Data field is to select the Sales Order Library, and drag the Inventory Library icon from the left side of the window onto the Form and let go. A table-like field appears. If you click the botom left corner icon (horizontal lines with a plus sign), a listing of the Inventory Library is displayed. Select the Inventory items you want to link to this Sales Order record and click "Save Changes". These records will now appear in the Related Data field and they are linked to the the Sales Order record. To summarize information from the Inventory Library, pull down the Insert menu and select "New Field...". Highlight "Calculation" and click "Continue...". Name the file, and under Available Fields, look for the Inventory Library, and all the fields will be listed beneath it. Select the field you want to summarize and double-click it. On the right side of the screen, it will show something like:
Count | Inventory | <field you want to summarize>
Click "Count", and change it to what you want to summarize. If you want the total, click "Sum". If you want the average, click "Avg".
You can then use this to summarize information from another Related Data field that links to a different Library.
Let me know if you need additional clarification.
TSGal
FileMaker, Inc.
Be the first to rate this
|
Sign in to rate this
Thanks so much for this. I am going to try it all out over the next couple of days and let you know how it goes.
Best,
Lucy
Be the first to rate this
|
Sign in to rate this
Hello again,
So I am trying it out and am making some progress but now I am stuck as I am trying to create a Bill of Materials section (Assembly of Raw Materials), for each Item. I can see how to incorporate the Materials table and Item table, but how do I include the QUANTITY of raw materials per item in order to get a per Iten Cost? And then I will need this amount of inventory of raw Materials to be deducted every time one of these items is produced (Production table?)
I am thinking that I will need the follwoing tables:
Suppliers (just to store all the contact details)
Customers (just to store the contact details)
Raw Materials (name, description, unit size etc)
Items (name, description etc)
Suppliers are related to Raw Materials
Raw Materials are related to Items via the Bill of Materials
Items are related to Raw Materials and/or BOM (product IN), and Customers (product OUT)
Does it make more sense for the Bill of Materials to be a form within the Item library, or as a separate library linked to Raw Materials and Item?
Every time an item is Produced, the Raw Materials inventory, and the Items inventory needs to reflect this, and ever time an item is sold, the Items inventory has to reflect this.
Thanks so much in advance for your advice!!
Lucy
Be the first to rate this
|
Sign in to rate this
Lucy Ashman:
Bento is not a relational database, but it does have some relational capabilities. However, assigning quantity to an inventory item is difficult. If you put it in the Items Library, this will be reflected each time that item is used. If you place it in the Raw Materials Library, then any time you reference that raw material record, it will have that quantity, and if you change it, the quantity will update across all records that use that record. In a Relational Database product, you would be able to make a many-to-many relationship and have the quantity in a separate table referencing the other tables.
One possible solution is to place duplicate records in the Raw Materials Library, each record having a different quantity. For example, a product may contain up to 5 widgets. Therefore, you would have five widget records in the Raw Materials Library, each record having a different quantity from 1 to 5.
If you need additional explanation, post a real-life example of a typical workflow, and I'll try to provide some insight.
TSGal
FileMaker, Inc.
Be the first to rate this
|
Sign in to rate this
I wanted to say thank you for all your support with my queries. In the end, after really trying to figuring ou a way to make it work for me (and I really, really wanted to use this because there are so many things I love about it) I had to bow out and find another solution.
best,
Lucy
Be the first to rate this
|
Sign in to rate this
I had this exact same query, and felt a bit cheated too. But then I thought about it, and realised that it is actually not good practice to have your raw material costs dynamically linked at every step - after all, what happens if the price of your raw material changes? - you don't want it also changing the values of orders that occurred in the past, before the update!
I think the best workaround is therefore to create your related data field and copy the current price into a new price column in the notes section of the related data field. You can then add a quantity here as well as a sub-total (as a calculation). And importantly, this won't change if the raw material costs change.
See screenshot attached.
The only shortcoming of this approach is that there is no way to link the data in the notes section to any other field in the form: it would be nice, for example to be able to have the calculated total linked to a calculation field in the form. So, FileMaker - please add this facility in!
Be the first to rate this
|
Sign in to rate this
Lucy, may I ask what the solution you went with was? I am currently trying to figure out if Bento will work for me. My dilemma is that I can't even play around with the trial version, because it won't work on my current computer (operating system too old). So to even try it out, I need to purchase a new computer. Big gamble, when I don't even know if it will do what I want!
TSGAL, what is a relational database? Can you give me an example that a layperson can understand? Thanks!
Be the first to rate this
|
Sign in to rate this
Embroiderer:
Thank you for your post.
In a Relational Database, tables (Libraries) are linked to each other via a common field (or sometimes fields). It may be easier if I provide a simple example.
In your Customer table, you have names, addresses, city, phone, etc. To make sure a person is unique, an ID is assigned. That way, if there are two people named "John Smith", they will have different ID's. In an Inventory table, you will have a product. Like customers, an ID is assigned. Now, to sell an inventory item to a customer, you could link the ID of that customer to the ID of the inventory, but what if the person wants 2 of these items? Should the information be stored in the Inventory or the Customer table? The answer is neither. Therefore, you may have an Invoicing table that connects the ID of the customer with the ID of the Inventory, and the Invoice would be the table connecting the two tables, where you would probably be selling to one person, but numerous inventory items. If the customer has several invoices, you could search for these in the Customer table, link through the Invoices (for the customer ID) and see all the Inventory items (inventory ID's) that were purchased through those Invoices (invoice ID).
To add more complexity, the Inventory table may relate to a Raw Materials table, and the Invoice table may link to an Accounts Receivable table that also links to the Customer table. The Raw Materials table may relate to an Accounts Payable table. Does this help at all?
TSGal
FileMaker, Inc.
Be the first to rate this
|
Sign in to rate this
Yes, thank you. That's an excellent explanation. So I am assuming that Bento can't do that? Does Filemaker?
Be the first to rate this
|
Sign in to rate this