5 Replies Latest reply on Apr 22, 2014 10:52 AM by philmodjunk

    Creating table made up of data from other tables



      Creating table made up of data from other tables


           I have 2 tables related through a third table. The linking table is called Orders, and the two others are Order Details and Payments.

           There are sometimes multiple order details per order; and multiple payments per order. I have since also linked the Order Details to Payments - so I know which payment is for which item. 

           But at the moment I don't have a table which shows all order details and their related payments. First of all I guess I should create this - but I'm not sure how to. Basically this wouldn't have any of it's own data in it - it would have all the order details data listed, and all the payments data listed. 

           Then I want to create a graph that will show quantity of stock of item x going out minus payments for item x. I want to see how much is the gap between stock out and payments and see whether the gap is closing or growing over time. I imagine I would create summary fields of running totals of each and then minus one from the other to find the gap. I would then do a monthly sort so the data comes out in a graph. 

           One other complication is that I would be sorting the Order Details data by the date of the order, and the Payments data by the date of the payment. I assume that to do this I would have to create a new field and work out of a way of grabbing the dates from their respective tables into one column. 

           Any help much appreciated!



        • 1. Re: Creating table made up of data from other tables

               You appear to have these relationships: (----< means "one to many" )

               Payments 2-------<?OrderDetails>----Orders------<Payments

               But what is the relationship between Payments 2 (a second table occurrence of Payments) and OrderDetails? Does a single payment pay for a single item in OrderDetails or a group of items in OrderDetails? (It seems very unusual to link specific payments to specific items listed in an order instead of linking the payment only to the order.)

               If the relationship between Payments 2 and OrderDetails is one to one, you can set up a list view layout based on either Payments 2 and include fields from OrderDetails or you can set up that layout based on OrderDetails and include fields from Payments 2. (But don't try to select fields from Payments.) But if one payment pays off multiple records in OrderDetails, then set up your list view layout to list records from OrderDetails with included fields from Payments 2.


                    Then I want to create a graph that will show quantity of stock of item x going out minus payments for item x.

               Sorry, but that sentence makes no sense to me. I don't see how you can subtract a dollar amount (minus payments) from a stock quantity.

               Did you mean that you want a chart of stock that is in inventory, but not counting an item as removed from inventory until it as been linked to a payment record?


          • 2. Re: Creating table made up of data from other tables


                 The problem with this is there is no simple way of defining the relationship between order details and payments. These are small items supplied to gift shops. The goods are supplied on consignment, and payments are made ad hoc. This means that most orders have multiple payments. On top of this, payments can overlap orders. 

                 So for example:
                 Orders for Item X by Company Y might look like:
                 Receives 12 items on 3/10; 30 on 7/11; and 15 on 4/12.

                 But payments for Item X by Company Y would be equivalent to:
                 Pays for 9 items on 5/11; 6 items on 15/11; and 3 items on 30/11; 5 items on 15/12.

                 This of course is a headache. I link all these two tables thru the Orders table, but with regards to the Payments table - this is a fudge, because a payment can be for some items from order 23 and some items for order 24 (but they will both be for the same product).  But I've worked out a way of making a layout on a Company basis which calculates total orders and total payments for those orders, and subtract one from the other. 

                 But I hope this helps to explain what I'm trying to do. I need to keep an eye overall on items delivered minus items paid for, and I don't think I can do this in the ways you have suggested. What I want is to have a list of quantities delivered of a set item by date. Then I want a list of items paid for of a set item by date paid. Then I want to minus the second from the first in a bar graph of monthly totals.

                 I have all this information, but in different tables - I've built my payments table so that payments are entered per product. So I know the payment of $27 on 5/11 is for 9 items of product x. And in my order details table i know quantities of what's gone out to each company.

                 So what I was imagining was that I could pull all this data into a new table to build the graph. I thought I would try and create one column to grab the dates - that would show the date delivered for products out from Order Details; and the date paid for them from Payments. Then sum running totals for items delivered and for payments made, and then subtract the second from the first. 

                 Sorry if this is not clear, please ask if this doesn't make sense. 





            • 3. Re: Creating table made up of data from other tables

                   I haven't really suggested much of anything yet as my first post was mainly an attempt to figure out what you have set up thus far and why. Your original description was impossible to do in FileMaker (you can't link tables into a circular relationship, FileMaker will insist on adding a second "instance" (table occurrence) of one of the tables.


                        I've worked out a way of making a layout on a Company basis which calculates total orders and total payments for those orders, and subtract one from the other.

                   A Payment can pay for more than one order and more than one order can be paid for by one payment. (And a payment can pay for just part of an order.) A many to many relationship is typically handled with a third table serving as the "join table" linking the other two tables.

                   "Selling on consignment" was one of the key missing details from your original post.


                   Payments::__pkPaymentID = Payment_Detail::_fkPaymentID
                   OrderDetails::__pkOrderDetailID = Payment_Detail::_fkOrderDetailID
                   Orders::__pkOrderID = OrderDetails::_fkOrderID

                   This is just the basic relationship. To avoid having to manually create records in Payment_Detail, other relationships and some scripts can be employeed to generate those records. But there are procedural issues to resolve before that can be done. If your order is for 10 of product X and 20 of product Y is this recorded as two orderDetails records? That's been my assumption here. And if you then receive a payment for a part of this order, how do you decide which items are now to be marked as "paid for" and which will not be until the next payment is received? Do you also receive info from the customer telling you which items were paid for by that payment?


              • 4. Re: Creating table made up of data from other tables


                     To clarify - Yes each order detail record refers to one product. And yes, I communicate with the customer to know how much of each payment is for each product. At the moment I only have one table instead of Payments and Payment details as you suggest. If a payment for 2 products comes in, I basically work out how much is for each product and record it as two different payments. It seems to work ok for now. 

                     At the moment I basically have a Payment table which reads:

                     Date paid/ Amount/ Product/ Quantity paid for/ Order:OrderID

                     And an order details table which reads:

                     Order: Date/ Product/ Quantity ordered/ Unit cost/ Order:OrderID

                     There are other fields too, but I think those are the pertinent ones. 

                     So I want to make is a table that looks like this:

                     Type/ Date/ Product/ Quantity/ Total stock out (orders minus payments)
                     Order - 15/12 - cards - 25 - 25
                     Payment - 17/12 - cards - 15 - 10
                     Order - 19/12 - cards - 10 - 35
                     Payment - 20/12 - cards - 12 - 23


                     What do you suggest?





                • 5. Re: Creating table made up of data from other tables

                       Whether you use a Payment_OrderDetail table or not, you need to be able to link payments to specific products listed in the order--which is why I show linking payments to OrderDetails rather than payments to orders as you will find in most other examples of an invoicing system as those examples aren't dealing with consignment sales.

                       My preference is to use the join table--especially if the number of different products that you sell on consignment are large. But if you want to use individual payments, you still have to link them to order details rather than orders to show which specific items ordered have been "paid for".

                       And this is just the basic set up in terms of tables and relationships. What I would imagine to be useful here is a layout where you log the new payment in a single record with the amount entered into a single field, select the customer to get a list of Products that were ordered by that customer and which have been ordered but not paid for and you then enter quantities for each product that the customer has told you that this payment pays for. The system then checks for inconsistencies such as not enough payment to cover the listed items to too much payment for the listed items and then generates the needed join table records for you.