11 Replies Latest reply on May 7, 2016 11:23 AM by tackermax

    Simple assets report by tablename

    tackermax

      I'm new to FM, and creating a filemaker 14 solution and the client would like a simple report where the break field is quite simply the table names.

      For instance, the entire database, all layouts sit on the Personal Information Table, which contains an auto-enter serial number ID field, called agreementID.

      Within each layout, there are portal tables related by a one to many.  One agreementID, many tables, with many assets in each table.

      i.e.

      Personal Information::agreementID = Real Estate::personalID

      Personal Information::agreementID = Insurance::personalID

      Personal Information::agreementID = Personal Property::personalID

      Personal Information::agreementID = Stocks and Bonds::personalID

      etc.

      These tables are all portals, and they need to be my headings in my report, listing out all their assets they have entered.

       

      What I need is a report with sub-summary partitions that will sort by the Table, and total all the assets from each table.  I currently have totals setup on each layout using summary fields.  However, the client really wants a report of all assets from all tables on one report for printing.  In addition, the client needs to add "for office use only" status fields on the report to be manually entered after the assets have been filled in. 

      My problem is, I do not have a break field and I do not know how to design this.  This is probably a very simple solution, but I can't seem to wrap my head around the concept.


      If I had a break field that was the table names, the report could be sorted out.  Is there a way to create a dummy table with values dependent on each tables values?  For instance, if the client enters Real Estate, the break field would include "Real Estate".  If they don't own any real estate, we would not have that value entered into the break field.  How do I create the right kind of break field for this report?

        • 1. Re: Simple assets report by tablename
          DanielShanahan

          There isn't much information on what fields you have in the portal tables (Real Estate, Insurance, Personal Property, Stocks and Bonds) but you do mention they all have a Total field.  I suggestion you incorporate all of this data together in one Asset table and differentiate them by a Type field (ASSET::type).  You can use that Type field as your break field.

          • 2. Re: Simple assets report by tablename
            erolst

            What's the difference between these tables? Are there differences?

            • 3. Re: Simple assets report by tablename
              tackermax

              Actually, let me update this... Each table is very different.  I would need one massive assets table with detailed names of columns like:

               

              bank_acct_owner

              stock_owner

              insurance_owner

              business_owner

               

              bank_balance

              stock_balance

              insurance_balance

              business_balance

               

              etc..

               

              So, if that's the only way to create a report based on asset_type, I can start creating that table and change the fields to the Asset table in all my portals. 

              • 4. Re: Simple assets report by tablename
                erolst

                tackermax wrote:

                Actually, let me update this... Each table is very different.

                Your example fields don't support that stetement; they seem to be identical.

                 

                tackermax wrote:

                I would need one massive assets table with detailed names of columns like:

                 

                bank_acct_owner

                stock_owner

                insurance_owner

                business_owner

                 

                bank_balance

                stock_balance

                insurance_balance

                business_balance

                 

                If you simply recreate all the fields from the other tables, you don't gain anything.

                 

                The idea is to have a generic asset table, where you have only one field per attribute (owner, balance, date), and one field (like assetType) that determines the type of the asset

                 

                If the asset of a given record is of type bank_acct, then that makes it clear that the balance field in that record represents a balance for a bank_acct – etc. etc.

                 

                Now you can use that AssetType field as your first level break field in a report.

                • 5. Re: Simple assets report by tablename
                  tackermax

                  Trying to explain better.

                  The fields I listed were not examples of "different" fields. 

                  However, I do have many different fields which is one problem. The other problem is, the way I have this laid out currently is that each asset type is a separate table with a separate portal to enter the multiple rows for each client.

                  If I use one table for all these portals, then any fields I consolidate, like "owner" and "balance" will show up in the other portal rows.

                  The fields are different enough for each table/portal that I couldn't fit just one portal in a layout.

                  I created an Assets table with all the fields from all the tables.  72 fields were necessary.

                  So, my remaining problem is, how to populate the "asset_type" field for each row.  I don't want to put a drop down in my Real Estate portal that asks "What type of asset is this", when it's obviously Real Estate.

                  • 6. Re: Simple assets report by tablename
                    DanielShanahan

                    tackermax wrote:

                    However, I do have many different fields which is one problem.

                     

                    Can you take screenshots of the field names so we can see the all the different fields in each table?

                     

                    Our suspicion (if I may speak for you as well, erolst ) is that the fields are not as different as you think.

                    • 7. Re: Simple assets report by tablename
                      tackermax

                      Screen Shot 2016-04-13 at 6.44.42 PM.png

                      Screen Shot 2016-04-13 at 6.46.28 PM.png

                      Screen Shot 2016-04-13 at 6.47.44 PM.png

                       

                      Screen Shot 2016-04-13 at 6.50.13 PM.png

                       

                      Here's a couple of the layout with two portals from 4 tables.  The first two are quite different, the second two are fairly similar.

                      Screen Shot 2016-04-13 at 6.52.40 PM.png

                      Screen Shot 2016-04-13 at 7.07.46 PM.png

                       

                      What I need to do is create a report that is populated by some key fields in each portal, plus some additional "for office" use only fields that will be drop downs indicating the current status for each asset.

                       

                      For instance the report would look like this:

                       

                      Properties

                      _______________________________

                      Address                                     Equity                                      Status                                    

                      37 Fowler Ln                             $400,000                                 Client to do                        

                      23 Summer Ln                           $300,000                                 Firm to do

                      _______________________________

                      sub-total                                    $700,000

                       

                      Bank Accounts

                      _________________________________

                      Bank Name                           Account                             Balance                           Status

                      wells fargo                           Partner 1                             $7777                              Client to do

                      elevations                           Partner 2                              $8888                             Firm to do

                      _______________________________

                      sub-total                                    $16,665

                       

                       

                      I guess I can just create a portal with all these fields and print it out, but I read that portals with printing doesn't work so good if the rows exceed the size of the portal.  So, if I have 5 rows of bank accounts, it won't print any rows that have to be scrolled down to.

                      • 8. Re: Simple assets report by tablename
                        DanielShanahan

                        Thanks, the screenshots and report samples are helpful.  A few more questions:

                         

                        1. Does the Stocks and Bonds summary report look like the Bank and Savings Account summary report?

                         

                        2. What does the Life Insurance and Annuities Policy report look like?

                         

                        3. How often is the report run: Daily, Weekly, Monthly, Quarterly, or Annually?  Or anytime someone wants to see the report data?

                         

                        4. What is the goal of the report: to show the bottom line (e.g. the subtotal) or to show the bottom line AND how it got there (i.e. the line items)?

                        • 9. Re: Simple assets report by tablename
                          tackermax

                          1.  Yes, the reports are similar.  Really the report only needs a few fields from the other tables.  Here’s an example of what the report should look like.  I created this last night.

                          Note that these are portals, not a report, so ideally, I my wish is to have this data in report format.   Notice how only 2 fields are populated from the Properties Table, Address and Equity.  The other fields are on all the tables so they can be added in this report.

                           

                          2. Report is run anytime someone wants to see the data.  It’s only ever run for one client at a time.  So, one agreementID value has multiple assets.

                          3. Exactly.  Bottom line, and how it got there.

                          • 10. Re: Simple assets report by tablename
                            DanielShanahan

                            The easiest thing to do is change the four different tables and combine them into one table.  Create a new field called "type" and use that as the break field.

                            • 11. Re: Simple assets report by tablename
                              tackermax

                              This was resolved with table occurances, with a relationship to a temp create column.

                              Thanks everyone!