1 2 Previous Next 17 Replies Latest reply on Jan 24, 2014 8:00 PM by GaryVogt

    Global fields

    GaryVogt

      Title

      Global fields

      Post

           I asked this once before and thought I had it figured out.

           One database.  Two different data field tables.  I merged two commonly used files into one so I could share info more easily.

           Database 2 has Summary and calculation results that I want to use in Database 1.

           I Created a Global field that was a calculation of the Summary/Calculation field that looks something like ...

                 Expense General (calculation) = x+y+z

                 Expense General_G (calculation, global) = Expense General

           It worked at first.  I was happy to see the fields with the correct values.  Then, adding more data and now the global fields are empty.  So, if I have a calculation field that has a result, why does the result for the global calculation go empty?  Especially if the Global field only has the same calculation?

            

        • 1. Re: Global fields
          philmodjunk

               I would remove the global storage option and leave it as unstored. Global calculation fields show data from the most recently modified record so they aren't likely to return a value that works for what you are doing.

          • 2. Re: Global fields
            GaryVogt

                 OK, well, I did that.  

                 If I just put the Summary field in, I get a blank.  i.e., nothing.  If I put in the Global version, I get $0.00

                 Once again, I have two databases in one.  I imported the whole (second) database into the first.  They each have their own Tables and they each have tier own Fields.

                 I want the Summary fields from the second database to be used in the first database.  

                 It's a shame Global fields aren't dynamic and store the information.

                 Now what?

            • 3. Re: Global fields
              philmodjunk

                   What's the relationship between the two tables? When you reference a summary field from a related table, the relationship controls what summary value is computed and returned as the value returned will be based on the number of records that match to the current record in the other table.

              • 4. Re: Global fields
                GaryVogt

                     I've tried connecting the fields through relationship, Global, non-Global, calculation, and just about everything else.  Bottom line:  I cannot get a summary field from the second database into the first database.  

                     I get an error message that says something like, you can't use this if it's not a Global field.  Or I just get a <blank>, $0.00, or <missing field>.

                     Maybe a Get field???

                • 5. Re: Global fields
                  GaryVogt

                       Nope, Get functions don't work either.  

                       I just want to use a summary field from one in the other.  Seems easy.  They are both in the same file.  

                  • 6. Re: Global fields
                    philmodjunk

                         To repeat: What's the relationship that you defined between the two tables?

                         Sorry but, "I've tried connecting the fields through relationship" doesn't mean that you used the correct relationship to get the results that you want. wink

                         A more complete description of the design of your database (why do you need two tables, for example...) would also be very helpful in helping me to help you.

                         More on how summary fields evaluate:

                         A "non Running" Summary field produces an aggregate value (a value from more than one field in one record). The value returned is determined by the context in which it is used/displayed:

                    Summary field is referenced on a layout based on the table in which it was defined:

                    A group within a FoundSet

                         If you place the summary field in a subsummary part that specifies the "break" field that grouped the records when the found set was sorted, you get a subtotal--the total for that group.

                         In a calculation, you can use the getSummary function to access the same group based sub total.

                    All the records in a FoundSet

                         If you put that summary field in a layout part other than the sub summary part, you get the total for all the records in the current found set.

                         If you refer to a summary field in a calculation field defined in the same table as the summary field, it will also return a total for the current found set. (Which is why we have the GetSummary function to get sub totals in calculations.)

                    Summary field is referenced on a layout based on a table related to the table in which it was defined:

                    Not in a Filtered Portal

                         If you place the summary field on a layout based on a related table or refer to it in a calculation defined in a related table, the relationship controls the value that is computed. It will be based on all the records in the summary field's table that are related to the current record in this table.

                         Think of it this way, if you put a portal on this layout to the summary field's table, you'd see all the records in this portal that are used to compute the summary field's value in this context.

                    In a Filtered Portal (FileMaker 11 and newer only)

                         If you place that summary field inside a portal with a filter, you no longer get a value based on all the related records. Instead, you see a value based on all related records for which the filter expression evaluates as True.

                         This is a special case use of a summary field that is often implemented by putting a single row copy of a filtered portal below it with the summary field inside so that the user sees a value based on just the records visible in the larger portal.

                         This is a "Display Only" trick as you cannot refer to the value of this field in a calculation and get the same value shown on the layout--you get the result described in "Not in a Filtered Portal" above.

                         Note that this does not just apply to "total" summary fields, Average, Count, Maximum, standard deviation, etc all follow these same rules.

                    • 7. Re: Global fields
                      GaryVogt

                           OK, well you've presented a lot of information.

                           1. You're correct in saying I may not have defined a relationship correctly.  Here is what I did.  

                           • I made copies of the summary fields I wanted to use in each table (I only did 3 each because I didn't have any faith that they would work).  Then, I used the relationship page to connect them.  What the hell, it was worth a try.  Still didn't work.  I'll admit that I don't use relationships enough to know how to use them correctly.

                           • Two tables:  Like I've said in just about every post, I combined two separate databases.  I had a primary database for income and expenses.  Plus, I had a secondary database with income and expenses for completely different applications.  The second database is a monthly tracking so it has over 200 fields with 12 repetitions each.  I use Sum calculations and Summary fields to collect the data.

                           For about 5 years, they were separate; really, didn't have a need to combine.  Then, About 3 years ago, I found myself writing Scripts to jump back and forth between two databases.  The need to have information readily available without jumping back and forth between two separate databases seemed like the solution.  I toyed with the idea of rewriting ALL 542 fields in the second database, which typically has about 60-75 records into the primary database which already has 520 fields but upwards of 800 records when I found the import button.  Worked like a champ.  Now, I could just jump between layouts in the same database.  I was a little disappointed the fields remained separate at first, but, after looking for specific fields, I'm glad they didn't.  Lots of fields to look through.  

                           • My found set is all records when in the primary database.  When in the secondary database, only those records are shown.  

                           • The Summary I use in the secondary database is a 'Total of' summary.  No 'Break fields.'  No 'Portals.'

                           • The secondary database has all of the data for monthly operations of a business summarized (fixed and variable costs of doing business if you will).  (Note: These are monthly expenses.  I use a lot of 12 repetitions so I can quickly see expenses for that item on a monthly bases.)  On the main page, I display the summary of each expense somewhat like you would a spread sheet with each of the 7 main types of expenses displaying the results of  3 to 15 separate subcategories, then totaled.  There is a 'Tally' column on the left which uses the same totals for the seven separate sections in an Income/Expense fashion with Net income at the bottom.  This page has always worked as designed.

                           • The main page of the primary database 'somewhat' resembles a Schedule C tax form.  The records in this database are only income from jobs and expenses for parts, labor, etc.  This database has been through many iterations because the tax laws keep changing.  This database, by itself, works great.  However ...

                           I want to display the results of the secondary database on this page as well so I can get a feeling for what my absolute bottom line is.  It would be awesome if I could just copy/paste the fields I need from the secondary database into the primary data base and be able to use them.  But I can't.

                           Each time I think I have it figured out, the field shows either <missing field>, or the calculation field says "this must be Global to do this or that" which still doesn't help, and so on and so on.  

                           OK, enough for now.  Let's see what I can figure out.

                      • 8. Re: Global fields
                        philmodjunk

                             Summary fields cannot be used as match fields in relationships. Since they don't store values specific to a particular record, it makes no sense to use them as match fields in any kind of relationship. Some other field that is NOT a summary field would be needed as the match field in a relationship. From the information posted thus, far, I can't suggest a relationship to use as it is not clear to me exactly how you need to link the tables in order to get the results that you want.

                             To me, it sounds like the best solution would be to merge the data in the two into a single table, one table for all income and all expense transactions. Other related tables such as one for income and expense categories would then be employed to help work with specific groups of records in such a combined table for reporting and data entry purposes.

                             Your mention of "540" fields and "fields with 12 repetitions", however, strongly suggests that you have other, major design issues with the design of your tables.

                             For a "bare bones" transactions table that I would use as the basis for this system, I would use a "ledger style" table similar to this demo file: https://dl.dropboxusercontent.com/u/78737945/AccountingLedger.fmp12 (Hope you are using FileMaker 12 or 13 or you won't be able to open it.)

                             Please note that I am not suggesting that this is the final solution to your issues, but that it may provide you with some ideas in how a restructured design could be set up and that your current data could then be imported into such a system.

                             And you can get multiple columns of data without using repeating fields. The layout and relationships are more work to set up, but the added flexibility in calculations, data searches and reporting is well worth the added effort. In my opinion, there are very very few uses for repeating fields in the current versions of FileMaker, they started making repeating fields obsolete with the first release of a relational version of FileMaker--FileMaker 3.0.

                        • 9. Re: Global fields
                          GaryVogt

                               I really have no desire to rewrite my databases.  I do have FM12.

                               When I added the relationship, I used the calculation fields, not the summary fields.  It still doesn't work.

                               I still don't see why I can't show a value, summary or otherwise, from one table on layout in the same database in which a previous table was made.

                               Yes, I could merge the fields I suppose.  Over a thousand fields in a database seems a bit awkward though.

                               I'm sorry you don't have a simpler solution.  It doesn't seem to me to be that big of a deal to get the field from one table to show up wherever I want it in a database.  I guess it is.

                          • 10. Re: Global fields
                            philmodjunk

                                 It's not a big deal and is quite simple to do--provided that the basic structure of your tables works for the type of relationship needed.

                                 But there is no way that I can tell you how to set it up from the information that you have provided so far. Please note that you have not identified which fields you used to set up your relationship that didn't work, nor what operators nor whether you selected a single pair of match fields or multiple pairs.

                                 Yes, I could merge the fields I suppose.  Over a thousand fields in a database seems a bit awkward though.

                                 I'm not at all suggesting that. Merging your data should move the date from fields in one table to fields that already exist in the other. That assumes a compatible structure in the two tables.

                                 Frankly 500+ fields suggests that you have way too many fields in one table and suggest signficant structural issues that should be resolved in order to produce a system that works as you need and does not require an extensive effort every time that you need to update your database design.

                            • 11. Re: Global fields
                              GaryVogt

                                   What more information do you need?

                                   Other than using a link (relationship) between customer numbers, I don't have any other relationships.  The fields I have are nothing more than fields and calculations and summaries.  Both work just fine as is.  

                                   I REPEAT, the only relationship between the two tables is the customer number.  There are no portals.  There is nothing else.

                                   Basic structure of a table:  Hmm.  I don't know.  Create database.  Create table.  Repeat for the second database.  Import #2 into #1.  Is that basic enough?  Here was the sample I created just to see if I could make ANYTHING work more than once.

                                   Database 2

                                      Field 1: Calculation, unstored, = Income from ... 1   (This one is a calculation of non-global data consisting of field_1+ field_2+field_3)

                                      Field 2: Calculation, unstored, = Income from ... 2   (This one is a calculation of global data using global versions of the same)

                                      Field 3: Calculation, unstored, = Income from ... 3   (This one is a calculation = summary data; i.e., summaries of field_1+field_2+field_3)

                                   Database 1

                                      Field 1: Calculation, unstored, = Income from ... 1   (This one is a calculation of non-global data referencing data in table 2, i.e.,  Title::Field 1)

                                      Field 2: Calculation, unstored, = Income from ... 2   (This one is a calculation of non-global data referencing data in table 2, i.e.,  Title::Field 2)

                                      Field 3: number                                                        (This one is just a number); just in case

                                   It isn't rocket science.

                                   500 fields in a database is too many?  Are you serious?  Database 1 has 5 repetition fields.  I use them to store part numbers, reference numbers, pricing, etc.  Are you telling me there is another way to store such data without creating hundreds of fields?  I'm listening.
                                    
                                   Why isn't data from one database accessible from another?
                                   Why isn't a summary field from Table 2, copied and pasted, from the second half my database, not available in the other half using Table 1?
                              • 12. Re: Global fields
                                GaryVogt

                                     The really odd thing is,

                                     Once in a while, the data in the global fields shows up.  

                                      

                                • 13. Re: Global fields
                                  GaryVogt

                                       Another thing.

                                       Within the same second half:  I have summary fields that work fine.  I made global versions of all of the data used in those fields and then made a global calculation field.  Sometimes it's 0, sometimes it's the correct value.  I also made global calculation of the summary fields just for grins.  Still, within the same database, that value will sometimes be correct and sometimes be 0.  Such as .. . . . . . 

                                       Second database.  Uses Table 2.

                                            Field_a, calculation, unstored, = e+r+t+y

                                            Field_aG, calculation, Global, = eG+rG+tG+yG

                                            ----------  Field_aG is sometimes 0.  Whereas, the Field_a is alway correct.

                                       How do I make the Global always show the same data?  If I could do that, I could use it in the first half.

                                  • 14. Re: Global fields
                                    philmodjunk

                                         We definitely have a communications gap here. I've actually been asking for the same information and not getting it several times. I realize that you think that you have answered my questions, but that's the communication gap. You haven't understood what info that I am asking for.

                                         Let's try taking a run at this from a different direction to see if we can clarify things or at least enable me to ask questions that you can answer:

                                         Start with a blank white page or computer screen.

                                         What do you want to see on that screen or printed page? A general outline/description of what you want to see and how it is to be presented can then set things up so that we can look at where the data needed to produce the results needed for the report is stored, what tables, what fields, etc. That in turn will dictate what relationship might serve to pull up the values you need in your report.

                                         But please note that from what you have described thus far, I suspect that the structure of your tables will greatly complicate this process.

                                    1 2 Previous Next