1 2 Previous Next 19 Replies Latest reply on Oct 25, 2014 9:25 AM by philmodjunk

    House Rental solution



      House Rental solution


      I am beginner with filemaker and would like to make a database for house rentals. I have 4 tables: Houses, Customers, Rentals & Expenses.

      My problem is how to define the relationships: some of the expenses are specific to certain rental (e.g. and therefore to the relevant house) and some are specific to house (with no connection to specific rental. e.g. city tax).

      However, I would like to see all the expenses either in one list or in lists by rentals.

      Thanks for helping



        • 1. Re: House Rental solution

          You can take one Tutorial: What are Table Occurrences? of Expenses and link it to Rentals and another occurrence of Expenses and link it to Houses. This puts all expense data into  one table which makes it easy to do a report of all expenses, but allows you to link some to a record in Houses and some to a record in Rentals.

          Caulkins Consulting, Home of Adventures In FileMaking
          • 2. Re: House Rental solution

            Thanks for your reply. My question is: How do I show all the expenses (from house and from rentals) with the related information such as: House Name, Rental Date, Customer Name, etc.

            I attach the relationship I created. 

            • 3. Re: House Rental solution

              Here is the relationships structure:

              • 4. Re: House Rental solution

                What you have can work for data entry, but you'll need a different group of table occurrences for your expense report. You'll need to use a layout based on an Occurrence of Expenses that is linked both to an occurrence of Rentals and Houses by the match fields shown here in use with different occurrences of the same Expenses table.

                You can then include fields from the related table occurrences as needed to fill in additional info about each expense.

                • 5. Re: House Rental solution

                  I understood your suggestion and it seems to work very well . However - if you look in the lower relationship in my graph: How do I make the house ID from houses2 to appear in the Expenses2 table. I need it in the expenses2 table in order to sort later the expenses by the houseID.


                  • 6. Re: House Rental solution

                    You don't need to. You can put Houses 2::__pkHouseID on your Expenses 2 based Layout, just like you can add fields from Expenses 2 to this layout. A subsummary layout part can even be sorted by the value of Houses 2::__pkHouseID in order to group expenses by that value.

                    But a calculation field can also copy over the value and might prove useful for certain reporting needs.

                    A calculation field with this expression:

                    Houses 2::__pkHouseID

                    and Expenses 2 selected in the "context" drop down will simply copy over the value of that field.

                    If ( IsEmpty ( _fkHouseID  ); Houses 2:: __pkHouseID ; _fkHouseID )

                    might also be used to produce a value that works for records created via the upper or lower table occurrence groups.

                    • 7. Re: House Rental solution

                      Thanks again for your explanation. I still can't get list of all expenses I need. Let me explain: some expenses are coming only from houses (general expenses e.g. city tax), and others are coming from rentals (and of course related to a specific house). I want to get list of all expenses and see in this list from each house each expense arrives, or from which rental and house it arrived.

                      It means that always the house number should appear in all records of the list (because each expenses comes from a specific house either general expense or rental expense) and the rentals appear only on rental expenses records .

                      • 8. Re: House Rental solution

                        What I have described should accomplish exactly that. Can you explain in more detail exactly where/how this fails for you at the moment?

                        • 9. Re: House Rental solution

                          The problem is that Houses 2::__pkHouseID exist only when there is a rental. I want to have a list of all expenses (either rental from lower (2) relationship or general from the top relationship)

                          • 10. Re: House Rental solution

                            I know, that's why I gave you this calculation field as a suggested solution:

                            If ( IsEmpty ( _fkHouseID  ); Houses 2:: __pkHouseID ; _fkHouseID )

                            If Houses 2::__pkHouseID does not exist, that expense record should be linked to a House via the upper table occurrence group. In those cases, _fkHouseID should not be empty. So the above calculation field can return the correct House ID for all records, whether created via the first or second set of relationships.

                            If you then add another occurrence of Houses and link it to this calculation field, you have access to all data from Houses that you might need for your report for both kinds of Expense records.

                            Note: apologies for leaving out the detail that you'd use this calc to match to another occurrence of Houses. Should have posted that info earlier.

                            • 11. Re: House Rental solution

                              Thanks alot, I figured out already what you mentioned in your last post.

                              Why when I make a layout based on expenses 2 (which is related to rentals 2 and also to Houses2 (as Houses2 is grandfather table of expenses2)), I can't use Houses2:__pkHouseID on the layout? It always appears empty...

                              • 12. Re: House Rental solution

                                If the current record in Expenses is linked to a record in rentals which, in turn, is linked to a record in houses, any field from Houses 2 should be able to display data from Houses.

                                If the above chain is "broken" at any point, the same fields will be empty.

                                • 13. Re: House Rental solution

                                  Thanks for your kind help and explanations

                                  • 14. Re: House Rental solution

                                    Now I have a design question:

                                    I made a layout based on Houses2 with portal based on rentals2. I want to add on the same layout another portal that will show dynamically the rentals expenses from expenses2. So when I am entering specific row in the rentals portal, I will see the related expenses in the expenses portal.



                                    1 2 Previous Next