10 Replies Latest reply on Feb 23, 2012 6:36 AM by MarcMcCall

    Calculation question

    MarcMcCall

      Title

      Calculation question

      Post

      Is it posible to use a field to store a calculation formula, so as to be able to change the formula in the field vs. under manage database?

        • 1. Re: Calculation question
          philmodjunk

          Yes, if you use a second field to display the value returned when it is evaluated.

          Example:

          You can define a text field named "Formula" and enter Qty * Unit Price in this field for one record and Qty + Unit Price in the same field of another record. You can then define a calculation field, Answer as: Evaluate ( Formula ) and in the first record you'll see the product and in the second record you'll see the sum.

          • 2. Re: Calculation question
            MarcMcCall

            Ok, I'm heading home for the night, internet is down, but I'd like to give you an example tomorrow and see if what I'd like to try and do is possible.

            • 3. Re: Calculation question
              philmodjunk

              To expand a little further on this idea, Evaluate expects an expression with the same operators and format you'd use in the specify calculation dialog. So if you can figure out how to write the expression inside the specify calculation dialog, you can then use this expression as data in the formula field.

              If you need a more user friendly format for your formulas, (user might not know that * means multiply and ^ is used for exponents), you can use text functions to take an expression entered/selected by the user and reformulate it into syntax that will work with the Evaluate function.

              • 4. Re: Calculation question
                MarcMcCall

                Let me try to explain this in detail, for you.

                 

                I have a Locations table linked to an Account table via ID_Account. Then I have a Census table linked to the Account table through ID_Account.  I have a field on the locations table called Location with a Building name and Floor "William James 1st Floor" or "William James 2nd Floor" and so on.

                When an account gets placed on the Census a new record is made for that Account on the Census table. From the Locations table I have a button that links the Account to the particular location. On the Account table I have a field called room that is equal to the location name through the ID_Account on the Locations table. Then on the Census table I have a field called room that is equal to room on Account through ID_Account.

                Then I have a flag calculation on The Census Table that looks for If(Status="Active" and Room = "William James 1st Floor" or Room = "William James 2nd Floor" or Room = "William James 3rd Floor";"1") Then I have a Count_William_James_Flag summary that is set to Count, so I can count the total people in that building. I have a set up like this for 10 different buildings.

                What I would like to see is possible, can I make them some custom fields so If they want to add some more building to the solution they can enter the flag calculation in a field and a field they can put the count calculation in, and a field they can use to enter a name label in to say what it is counting on a layout. Right now I have the Locations name set up as an editable dropdown value list, but if they add any new locations there will be no calculations to count it on the Census Cover sheet I have set up. On the cover sheet I have 3 rows of fields set up. The count of how many are being occupied - how many rooms total to get what are available.  I have a small popup wind on the locations table that only has building name lables with a dropdown lists with numbers in it and it is set to global and thet Count_Flag is used agains this number to give me the available. If it is possible, I would like to set it up so I could allow them to add 4 more buildings and be able to flag them and count them and put a lable on the cover sheet with possibly a conditional format set up so that if it is empty, it is all transparent and cant be seen on the cover sheet.

                 

                I hope that is enough info but will be happy to eloborate more if needed.

                • 5. Re: Calculation question
                  philmodjunk

                  Census::ID_Account = Account::ID_Account = Locations::ID_Account

                  From the Locations table I have a button that links the Account to the particular location.

                  From what you have described, you are actually linking a location record to an account. To link an account to a location would require a relationship based on a location ID instead of an Account ID.

                  Account::Room is a calculation field defined as Location::LocationName

                  Census::Room is defined as Account::Room

                  Note that one field, Location::LocationName, can be used everywhere that you are using a "room" field from the other two tables, they are not needed unless I have missed some detail here.

                  Then I have a flag calculation on The Census Table....

                  Oh my, you can use Count ( Location::AccountID ) to count all location records for the same account and you can also set up a summary field in locations that will compute the same count of locations per account when you reference that field from either Account or Census.

                  In fact, this looks like a report that should be created from the locations table if I have correctly analyzed your design. You can sort your location records by account and use a sub summary part (when sorted by AccountID (or a related name field from Account) ) with that summary field to produce a count of the number of locations records for each account. The individual location records can be listed or you can remove the body layout part to just list the totals for each account.

                  • 6. Re: Calculation question
                    MarcMcCall

                    I may have explained the Id links incorrectly. There will only ever be one location attached to an account.  As soon as they leave I remove the link and when someone else comes and occupies that location I link it to their account.  There is only ever going to be one account per person, but if they ever come back a new  Census record is created  to archive the data from the last time they were here.  I have more tables and all feed into the Census Table for calculations. The census cover sheet displays ALL calculations.  It is an over view of all people on site.  Their location,their emploment status, foodstamp status, progress through the steps.  It has average age, averge stay lenght average 14 days or less, how many people in each building how many people on blackout, if someone has been there for 60days and not completed certain steps flags pop up, somewhere around 60 or so calculations giving them the pertinate data that they want to be able to see on one sheet. All of the data will be maintained for their archives except for building location, that is why I have it set up to just link them to a location then remove the link when they leave.

                    • 7. Re: Calculation question
                      philmodjunk

                      Still, such complex and inflexible calculations should not be needed for your database to work.

                      What does one record in Census represent?

                      I don't see how it's possible for room to return more than one value from location for any given account if it is a calculation field. If it is a text field with an auto-entered calculation, then that might explain how you might get more than one value in Room.

                      • 8. Re: Calculation question
                        MarcMcCall

                        The Census Record is where thier employment status is input, their progress through the steps.  (might help if I fill you in, the place that I'm developing this for is a 12 step long and short term recovery community.)  The Census Keeps track of whether they are in the program, finnished with the program but still on site, lenght of days, if they have a sponsor or not, if they are on blackout(14days or less) pulls info from the food stamp table as far as status, legaly they have to keep the old food stamp records as far as status and other reasons, that is why it is on its own table, to maintain the old records.  They have a lot of returning people so Census record is maintained, and a new one created for each stay, that way they can track how a person may have changed since the last time.  Plus with all of the archived data they can review if there are any statistical changes that may apear if they change a program proceedur, the drug use statistics, age statistic, all sort of usefull info in the field of helping people get sober.  I built them the one they are using now, but when I did, 3 years ago, I didn't even know what a data base was, It has many flaws, and this one has substantially fewer and has more data available, and the bigest downfall of the origional was the locations. All it had was a field with a drop down list with a building name, another field with a floor#. the whole database was built on one table.  Very hard to maintain any data because there were no relationships you just overwrote the origionl data with new.

                        • 9. Re: Calculation question
                          philmodjunk

                          Does one record in Census represent a single person or multiple people?

                          If the same person stays enrolls in your program, you have three census records for that person, correct?

                          I'm trying to parse the logic behind your calculation. How do you use it in a report?

                          A summary report based on the Census table can be set up with sorting and sub summary parts to produce this kind of report:

                          William James 3
                          John Jones      2
                          Fred Smith      3

                          Where the number is the count of census records for that client.

                          Is that what you are trying to produce here?

                          • 10. Re: Calculation question
                            MarcMcCall

                            Thanks for getting me thinking, I came up with a solution that will allow me to do what I was wanting.