1 2 3 Previous Next 35 Replies Latest reply on Sep 18, 2013 1:21 AM by BatuhanGizer

    Showing a new record from multiple databases in one

    BatuhanGizer

      Title

      Showing a new record from multiple databases in one

      Post

           Hello everyone,

           I am trying to create a new system with multiple databases and I will try to explain my problem to the best of my ability.  

           I already have a database called Projects in which all my projects, with a unique project ID, are stored with their information.  

           There will be 4 other databases naming <Accounts> <BankAccount> <Invoices> and <Checks>.

           Each of these will include a ProjectID, AccountID, IncomingAmount and OutgoingAmount fields and <Accounts> database only will show records from other databases and no new record will be added by user(s). What I am trying to do is, by an example;

           Project ID and AccountID will be entered to <BankAccounts> <Invoices> and <Checks> databases when an incoming or outgoing payment is made. If an invoice is received, user will enter ProjectID, AccountID, Date ect. to the <Invoices> datase and enter the inovice amount to IncomingAmount field ONLY.  If the invoice is going to paid by a check, user will enter ProjectID and AccountID, CheckDate, etc. to the <Checks> database and enter the check amount to the OutgoingAmount field. If it the payment is made by a wiretransfer or if an amount is paid to our bank account, user will enter the ProjectID and AccountID for which the payment is received for, again will enter the amount to IncomingAmount or OutgoingAmount.  

           The thing I am trying to figure out (or learn since this is kind of getting complicated for me) is that all of the user entered values from <BankAccount> , <Invoices> and <Checks> according to their ProjectID and AccountID will be seen under <Accounts> database and will be sorted by ProjectID first then by AccountID.  

           For example, a payment to an AccountID can be made for several ProjectID's.

            

           <Accounts> database should (basically) look like this after user enters the following information to <Invoices> database.

           Project ID      AccountID    IncomingPayment      OutgoingPayment

           101                    NEL                1000

           Then, if this invoice is going to be paid by the bank account, user will enter the ProjectID, AccountID and outgoing payment to <BankAccount> database and <Accounts> database will look like;

            

           Project ID      AccountID    IncomingPayment      OutgoingPayment

           101                    NEL                1000

           101                    NEL                                                         1000

           The AccountID can be used with different projects as well. So in order to prevent any mistakes, I will add sub-summary fields when sorted by ProjectID then AccountID. 

           Sorry for the long post and hope that I was clear about the explanation.

           Thanks in advance!

        • 1. Re: Showing a new record from multiple databases in one
          philmodjunk

               Can you explain the difference between your Account and BankAccount tables? In your example, they appear to record identical information and require that you enter the same data twice.

          • 2. Re: Showing a new record from multiple databases in one
            BatuhanGizer

                 Accounts will be the end result.  There will be no user entered data in Accounts table.  It will gather all the information from BankAccount, Invoices and Checks tables and will show every IncomingPayment or OutgoingPayment fields seperately in order to track each of them.  In my example, the payment can be paid by a Check in which case the user will enter ProjectID and AccountID to Checks table.  After user enters, whether it is incoming or outgoing, Accounts table will show the payment sorted by ProjectID and AccountID.

            • 3. Re: Showing a new record from multiple databases in one
              philmodjunk

                   I'd suggest using a single table to record the receipt of all payments--whether checks, wire transfers, or any other methods. Use a field or two in that table to identify the payment specifics such as an account number, check number etc. This will simplify your relationships and make some types of summary reporting such as a report that summarizes all your gross income with subtotals for each month, easier to set up.

              • 4. Re: Showing a new record from multiple databases in one
                BatuhanGizer

                     I also thought about what you said and agree that it will make everything alot more easier but the users that will use this system are not very associated with filemaker and my boss wants this set up as the way I described.  :( 

                     Is there any possible way of doing this system, such as moving a record/creating a duplicate from one table to another? Since the ProjectID and AccountID will be the distinguishing factors, I can then get subtotals etc. in Accounts table with every information entered from other 3 tables rolled into one. 

                • 5. Re: Showing a new record from multiple databases in one
                  philmodjunk

                       Never allow the client to dictate the tables and relationships. They can't see them anyway. What they see are layouts and you can set up your interface such that they have their different layouts for different payment methods and you can set this up so that they can't see the other payment type records even by accident, but the data all goes into the same table.

                  • 6. Re: Showing a new record from multiple databases in one
                    BatuhanGizer

                         Ok so let me ask another question since the first way that I described was very complicated.  Lets say that I have 3 different layouts as I wrote them: BankAccount, Invoices and Checks. Invoice layout will only have IncomingAmount field. Checks layout will only have Outgoing field.  Is there anyway to make a script/layout/report which will show every ProjectID and AccountID subtotals? For example; I will enter 1500 to IncomingPayment in Invoices layout with ProjectID 101 and AccountID NEL then I go to Checks layout and enter 1000 to OutgoingPayment with the same ProjectID and AccountID.  But I can use the same AccounID with another ProjectID and do the same process except with a ProjectID 102 with the IncomingPayment as 2000 then on the ChecksAccount with ProjectID 102 and AccountID NEL, enter Outgoing Payment as 500.  Is there anyway to get a report looking something like this;

                          

                         Project ID      AccountID    IncomingPayment      OutgoingPayment     Balance

                         101                    NEL                1500

                         101                    NEL                                                         1000

                                                            Total:      1500                                1000                          500      

                         Project ID      AccountID    IncomingPayment      OutgoingPayment     Balance

                         102                    NEL                2000

                          

                         102                    NEL                                                         500

                                                            Total:      2000                                500                          1500      

                    • 7. Re: Showing a new record from multiple databases in one
                      philmodjunk

                           What you show is a classic summary report. You use a list view layout and add a sub summary layout part (use the Part Setup.. dialog) "when sorted by Project ID" for the header row with the "print above" option. Repeat this to add a "print below" sub summary part with the same "when sorted by" option specified. Put summary fields defined to total the payment columns in this sub summary layout part and they will display the total for that project ID.

                           To get the balance, define a calcualtion field that you do not put on this layout as IncomingPayment - OutgoingPayment. Then define a summary field that comptues the total of this calculation field to add to your sub summary part.

                           Note: if you make a second summary field to total this balance calculation field, you can define it to compute a running total for use in the balance column so this looks like a standard bookkeeping "ledger".

                           If interested, here's a tutorial on creating summary reports: Creating Filemaker Pro summary reports--Tutorial

                      • 8. Re: Showing a new record from multiple databases in one
                        BatuhanGizer

                             Ok so in order to have a better understanding of what you are describing, I am going through the tutorial.  But, I didn't understand one thing which is after this part;

                             " Now we’ll create a simple summary report for printing out our line item data: 

                               
                        1.           
                                         Select your LineItems layout and enter layout mode.
                               
                             Am I creating a New Layout/Report when I start doing this or go throught these steps within LineItems table's "edit layout" section? 
                        • 9. Re: Showing a new record from multiple databases in one
                          BatuhanGizer

                               Please ignore my last comment.  I did the tutorial and made some changes as can be seen in the image I uploaded.  I just added QtyGiven and QtyBalance and made summary fields that sums these fields.  In my new layout, I get totals of Qty and QtyGiven but I get the total for all records in the QtyBalance field in the sub-summary(PartNo).  

                               By the way, the total balance which is 11 in the screenshot is the totalBalance in LineItems table.  I didnt get the screenshot of everything.  Just to prevent any confusion.

                          • 10. Re: Showing a new record from multiple databases in one
                            BatuhanGizer

                                 Hello, 

                                 I think I have a better explaining as to how to solve what I want to achieve. Ok so from scratch. Firstly, we have 3 different ways of payments.  First is by check, by wiretransfer or cash which is from our main office. Tables that I want to have are; 

                                 Table           --->            Fields

                                 Project                          ProjectID, ProjectName, ProjectAdress

                                 Account                        AccountID, AccountName, AccountAdress

                                 Invoice                          ProjectID, AccountID, InvoiceNumber, InvoiceDate, DebitAmount, CreditAmount

                                 Checks                          ProjectID, AccountID, CheckNumber, CheckDate, DebitAmount, CreditAmount

                                 Cash                              ProjectID, AccountID, Date, DebitAmount, CreditAmount

                                 WireTransfer                ProjectID, AccountID, Date, DebitAmount,CreditAmount

                                  

                                 I have 2 projects, 101 and 102 and I have 3 accounts that we take invoices and make payments, A,Band C. For instance, we receive an invoice from A account for the project 101.  The user will go to the Invoice table enter the information for which the invoice is received for;

                                 Project ID            Account ID        InvoiceNumber        InvoiceDate         DebitAmount      CreditAmount

                                 101                            A                       123456                    7/22/2013              1.000                           0

                                 We decided to pay the half of this invoice with check and the other half by cash. The user then will go to checks table and create a new recod, enter the following,

                                  

                                 Project ID            Account ID        CheckNumber       CheckDate             DebitAmount      CreditAmount

                                 101                            A                       112233                    7/30/2013              0                                500

                                 then will go to table Cash and create a new record enter the following;

                                  

                                 Project ID            Account ID             Date                          DebitAmount                  CreditAmount

                                 101                            A                       8/1/2013                                                                 500

                                  

                                 The user does the same thing except with a different ProjectID but to the same Account and the InvoiceAmount is 2.000 on 8/2/2013  and 1.000 payment was made by Cash on 8/15/2013 and 500 on 8/20/2013 with wire transfer so the balance is 500.

                                 The problem I am having is Cash table is not always bound with Invoice or Check table.  There can be debits and credits not related to an Invoice or a Check but all amounts in all of the tables are related with a ProjectID and an AccountID. I want to make a report that looks something like this, 

                                 ProjectID           AccountID           Date          DebitAmount           CreditAmount        

                                 101                         A                  7/22/2013          1.000                                                           

                                 101                         A                  7/30/2013                                               500               

                                 101                         A                  8/1/2013                                                 500            

                                                                                                                     Balance:                     0    

                                 102                         A                   8/2/2013            2.000

                                 102                         A                   8/15/2013                                             1.000

                                 102                         A                   8/20/2013                                               500

                                                                                                                     Balance:                   500

                                 and so forth.  I haven't started this on FileMaker yet.  I am trying to figure this out on paper but couldn't figure out how to do the relationships.  I hope this was a better explanation as to what I want to do. If you can help me with on how to do the relationships and how to implement this, that would be great.  

                                 Thanks in advance!

                                  

                            • 11. Re: Showing a new record from multiple databases in one
                              philmodjunk

                                   I recomend that you put all payment records in one table as this will make your reporting task much easier. A field in this table can identify the type of payment and the few fields that are only used for certain types of payments can be left empty for payment records of other types. If needed, you can also link in tables for just those details that are specific to one payment type, but from what I see here, it looks simpler just to keep a few extra fields in a combined table of payments that you use only for certain payment types.

                              • 12. Re: Showing a new record from multiple databases in one
                                BatuhanGizer

                                     The problem with what you are describing is that when I enter an Invoice for a ProjectID and AccountID, the thing that I am trying to achieve in Cash table becomes impossible.  The Cash table, which keeps track of the cash payments from our office, is not related with any of the Invoices entered.  Invoices are for Accounts only but Cash table is for our record keeping in order to track our Cash availability.  For the payments, I think what you described, is great and makes it alot more simpler relationship wise. 

                                     With the Cash table, the credit field should go through the Accounts table but the problem is debit field.  

                                • 13. Re: Showing a new record from multiple databases in one
                                  philmodjunk

                                       I don't see why that should be a problem. All payments are payments, whether cash or via other means. All payments should have the option of being linked to one or more invoices. In many cases, this requires a join table between payments and invoices so that you can manage cases where a payment pays off more than one invoice or is a partial payment applied to a single invoice. And your payments table can definitely be set up to with both a debit, a credit and balance fields so that it functions like a typical bookkeeping ledger. Debit entries would not be linked to an invoice (the fk field would remain empty) unless you are applying some type of discount or refund to a specfic invoice or group of invoices.

                                       Invoices-----<Invoice_Payment>-----Payments

                                       Invoices::__pkInvoiceID = Invoice_Payment::_fkInvoiceID
                                       Payments::__pkPaymentID = Invoice_Payment::_fkPaymentID

                                  • 14. Re: Showing a new record from multiple databases in one
                                    BatuhanGizer

                                         It seems to work as I wanted and that last comment was actually very helpful regarding to my next question! :) 

                                         Thanks alot!

                                    1 2 3 Previous Next