9 Replies Latest reply on Dec 4, 2008 9:47 AM by TSGal

    embedding Excel SS in record

    whowland

      Title

      embedding Excel SS in record

      Post

      I'd like to be able to embed an Excel SS in the records I use to keep track of billable hours for different clients. The records presently contain mostly text, date, and calculation fields, but I want to be able to note the time I spend too, so I can then call up each client's totals for billing.

      I tried to copy a small SS into a container field but nothing worked as hoped. Ideas?

      thanks

      Wendie

        • 1. Re: embedding Excel SS in record
          TSGal

          whowland:

           

          Thank you for your post.

           

          When you insert the file into the container field, check the option at the bottom of the dialog box to "Store only a reference to the file".  Then, you can double-click the file in the container field, and the spreadsheet will open in Excel.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: embedding Excel SS in record
            whowland
              

            I don't think I made myself clear. I need to have a small SS for each record, and I don't want to have to maintain a separate set of worksheets in Excel elsewhere to which a link will refer if I don't have to. Can't I somehow paste a blank, but fully functional, spreadsheet into the template for new records, and when a new record opens, put the SS to work in situ?

             

            w

            • 3. Re: embedding Excel SS in record
              TSGal

              whowland:

               

              Thank you for the clarification.

               

              No, you cannot "paste a blank, but fully functional, spreadsheet into the template".  However, you could set up a portal into a related table that acts like a spreadsheet.  Naturally, you wouldn't have the same spreadsheet functions, as you would be limited to the calculation functions of FileMaker Pro.  What functions did you want to use?

               

              TSGal

              FileMaker, Inc.

              • 4. Re: embedding Excel SS in record
                whowland
                   All I'd need is the ability to add up the column for billable hrs and the column for charges. If I can't paste in a functional blank SS, can you tell me how to have something that looks/works like it in FMP?
                • 5. Re: embedding Excel SS in record
                  TSGal

                  whowland:

                   

                  It sounds like you aren't doing anything complex, so this is definitely doable within FileMaker Pro.

                   

                  Since this data is going to be occurring in a portal, you will need to set up another table and link the main table to this sub-table.  Therefore, you are going to need a key field that is unique for each client.  If you already have one, then we'll use that as our key field to link to the other table.

                   

                  Pull down the File menu and select "Manage -> Database".  Click on the "Tables" tab, and create a new table "SS".

                   

                  Next, click on the Fields tab and be sure to enter the key field name that is either exactly or closely resembles the name of the key field in your client table.  Then, create the fields that you would like to see appear in the spreadsheet (billable hours, charges, etc.).  If your spreadsheet adds values across the row, then you will create a calculation field to add/subtract values.  If you are going to be adding up values downward, we'll handle that in the main table.

                   

                  Once finished, click on the Relationships tab. Find the key field in one of the tables, click on it and drag it to the key field in the other table.  Let go, and a line appears connecting the two tables together.  In the middle of that connection, there is an icon.  Double-click on that icon, and this is where you edit the relationship.  At the bottom, be sure to check the option to allow creation of records in this table via this relationship.  Do this on both the left and right sides.  Click OK twice to return to Browse mode.

                   

                  Pull down the View menu and select "Layout Mode".  On the left side of the screen, click on the icon just below the oval tool.  This is the portal tool.  On your layout, draw a box where you want your spreadsheet to appear.  You draw from upper left corner to lower right corner.  Once you finish drawing, a dialog box pops up (Portal setup).  At the top, you want to show related records from "SS".  Depending on how many records you want to show in the portal, you may want to show a scroll bar and/or have the ability to delete related records (rows).  The options are left up to you.  You can always go back and change the options.

                   

                  Click "OK", and the listing of fields from SS are shown.  Select the fields you want to display in the portal and when finished, click "OK".

                   

                  Pull down the View menu and select "Browse Mode", and you should be good to go.  You can start entering data immediately into the spreadsheet.

                   

                  Let me know if you need clarification for any of the above steps.

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: embedding Excel SS in record
                    whowland
                      

                     

                    Did you mean, create a new table in which one field is "billable hrs" so I could then connect the "billable hrs" field in the main table to it? 

                     

                    I'm trying to do this, but when I have the two tables side by side ready to make the connection, the original table's line for "billable hrs" is greyed out so I can't connect to it. In the original layout it's presently a container field-- should it be something else?

                    • 7. Re: embedding Excel SS in record
                      TSGal

                      whowland:

                       

                      Sorry for the confusion.

                       

                      Create a new table where you can display a portal.  You will need one key field that identifies the spreadsheet records with the current client record.  Possible a Client ID field?

                       

                      A container field would not be a "key" field.  You cannot graphic objects as key fields.

                       

                      I would not use "billable hours" as a key field, because I'm sure it would change.  I thought billable hours is a field you wanted to use in your spreadsheet.  Now I'm confused....

                       

                      TSGal

                      FileMaker, Inc. 

                      • 8. Re: embedding Excel SS in record
                        whowland
                          

                        OK, I think I understand what you're telling me.

                        I'm creating a new database, so I have no records yet.

                         

                        My original table has (for simplifying purposes), name, client, date contract rec'd, and notes.

                         

                        I have made a new table with name, date, time, purpose, billable hrs, and charge. I will want to be able to enter data on these and total the columns for billable hrs and charges.

                         

                        I have done the relationship thing and drawn the line between the two "names" fields

                         

                        Now when I open the DB to layout or browse, every field says <Table Missing>. Is this supposed to happen?

                         

                        The Portal Set-up window is all greyed out and shows "show related records from <unknown>" but won't let me tell it to use the other table

                         

                        I seem to have missed something critical here, so sorry....

                         

                        ???

                        • 9. Re: embedding Excel SS in record
                          TSGal

                          whowland:

                           

                          It sounds like something went askew with the definition.

                           

                          In your original layout, make sure the name is visible.

                           

                          Next, go into Layout mode, and double-click the portal.  Make sure the portal is set up to display information from the related table.  Once set, click OK.

                           

                          Return to Browse mode.  You should be able to click into the first row of the portal.  You mentioned that the Name field is included in the portal.  This is not necessary.  When you click on a new record in the portal, the name field will fill in once the record has been committed.

                           

                          If the portal window is grayed out, then create a new portal and set it up again.  Once satisfied, you can remove the old portal.

                           

                          TSGal

                          FileMaker, Inc.