12 Replies Latest reply on Aug 5, 2014 1:20 PM by briancrockett

    Calculation for text fields



      Calculation for text fields


           Hello. I've been trying to find any similar posts in hopes in finding an answer, but can't seem to find what I'm trying to do, which is probably easy. I've tried using the "If (IsEmpty...)" function for this, since I have 5 possible fields that could be filled out with any sort of text, and need a field that will calculate if any one of these fields are filled out. I've done this with numbers, but not with any text. This is what I've tried to use for a calculation, which doesn't work or I'm missing something:

           If (IsEmpty ( Department_Kiosk1); 0 ; 1 + (IsEmpty ( Department_Kiosk2); 0 ; 1) and so on.

           Note: Have the calculation result a a number; Number of repetitions: 1; Have it checked to not evaluate if all reference fields are empty.

           Trying to come up with, if any these fields are empty, it's counted as zero and if it is filled out, it counts it as one. Image attached is showing the 5 fields that could possible be filled out , along with the field that I'm trying to get this calculation to work for. Any help is greatly appreciated.


        • 1. Re: Calculation for text fields

               Your 5 fields appear to be something better set up as 5 records in a related table instead of separate fields.

               Am I correct that you want the number of fields that are not empty?

               If so....

               Count ( Field1 ; field2 ; field3 ; field4 ; field5 )

               should return the value that you seek.

          • 2. Re: Calculation for text fields

                 These five fields are related to each record I have in my database, so don't believe that these should be 5 records for each of my dept. kiosk fields. And to answer your last question, I want my calculation to come back with a number of kiosks that are in the record (which is a store in my case.) So if 3 of the 5 fields are filled out with any sort of text, then the calculated result should come back as 3. Or if none of the fields are filled out, then it should come back with 0 (zero). Will this calculation you provided work with what I'm trying to do based on my current answer?

            • 3. Re: Calculation for text fields


                   not IsEmpty ( Department_Kiosk1 ) +
                   not IsEmpty ( Department_Kiosk2 ) +
                   not IsEmpty ( Department_Kiosk3 ) +
                   not IsEmpty ( Department_Kiosk4 ) + 
                   not IsEmpty ( Department_Kiosk5 )
                   You don't need the IF statement.
                   Boolean functions resolve to 1 if TRUE and 0 If false. You need only to add them together. I tried the above calc and it works.
                   I agree with PhilModJunk, you should do this through a relationship. Eventually you will have Department_Kiosk6, 7 & 8 and you will have to change the calculation. If you or your successor forget to do this your data will be wrong. 
              • 4. Re: Calculation for text fields

                     If I've read your comments correctly, you need a relationship like the attached picture. Then assuming you wouldn't make a record where you didn't have a kiosk, then the following calculation would work.


                This would give you a count of all the kiosks for each store regardless if you had 1 or a 1000. 

                I try never to have a fixed set of fields as it makes your solution too hard to maintain. The only time I'd do this is for days of the week or months of the year. It's unlikely you're ever going to have 8 days a week or 13 months in the year.

                • 5. Re: Calculation for text fields

                       So from your last comment/image, I would take my kiosk fields out of the table I currently have it in and put it in it's own table and link it back to the main table by store number (which is in both tables) where it would list all the kiosks for that store, correct? Then for the qty of Kiosks for that store, wouldn't I still have to use the calculation you provided earlier? I'm trying to wrap my head around how to give each kiosk it's own ID for each store or have a standards ID code for each dept. where in each store that has it in that dept., it's linked by store number, right? Am I getting close to what you're thinking I should do in this case?

                  • 6. Re: Calculation for text fields

                         You're getting there. 

                         But you'd be using  Count ( Kiosks::Kiosk_ID )

                         If you enter the kiosk data through a portal in the Store table it will manage the Store_ID_fk for you provided you set up the relation ship as shown.

                    • 7. Re: Calculation for text fields

                           Here's what it would look like. I've included the kiosk ID but that doesn't have to be in the portal. As long as you have one writable field in the portal. 

                           How it works is when you type into the empty portal row and click out side of the portal a new Kiosk record get's created and automatically gets the Store_ID.

                      • 8. Re: Calculation for text fields

                             I'm trying to mimic what you described and I have one question for you. For the Kiosk ID, what function/calculation would I use for that field to denote the specific dept. areas if I type in a certain Kiosk ID for a store? For example, based on your image, let's say, your kiosk ID 1 = Shoes dept. (all the time), kiosk ID 2 = Men's wear, etc.  Is that how you have it set up now? Here's what I have so far, but nothing is showing up in my portal for the dept that I typed in, but I'm hoping I won't have to type it in, if I can get my first question answered where I would type in a certain Kiosk ID for a store record and the dept. would just populate, similar to how the store name populates after I typed in the store number. First image will show Browse mode where I added 3 records of the kiosk location for one store. Second image, once I post it, will show in the "edit layout" mode. I know why it won't populate in the portal, since it's looking to my main store table that you said to move out the kiosk information into it's own table, which I know you can't have a portal of records of the table you're in. So I must have something set up incorrectly or interpreted something wrong. Hope this makes sense. Thanks for your help.

                        • 9. Re: Calculation for text fields
                          /files/55fd86e4eb/Kiosk_Portal_2.jpg 1045x490
                          • 10. Re: Calculation for text fields

                                 Every table should have a unique identifier for each record even if you're not currently using it. Chances are some time in the future you will need it and if it's missing it a problem.  If you've set it up correctly if you search by any Kiosk_ID you will only get a single record back. Store_ID must also be unique. If you have two stores with the same Store_ID your relationship will not work properly.

                                 Kiosk_ID is a meaningless auto enter serial number that does not map to anything in the real world. It is unique per kiosk. If you need to number your kiosks, in a meaningful way you can add another number field and show it in the portal.  Example 5th kiosk in the store.

                                 Store_ID, Store_ID_fk, & Kiosk_ID do not need to be shown anywhere. They are for the background operation of the solution.


                                 Looking at your picture. You have a lot messed up. You seem to have it backwards. 

                                 The portal should be on a layout associated with the Store table. The portal should be associated with the Kiosk table. Store is the parent record and Kiosks are the children  or there is one Store for many kiosks.  Portals are for the children/many records.

                                 See attached layout



                            • 11. Re: Calculation for text fields

                                   I think I got it fixed, except for the count calculation, which I used what you noted earlier, I believe (see image attached.)

                                   Also, since the Kiosk ID would be it's own ( I have it to auto enter and will remove from portal, since it means nothing at this point for my purpose for the layout), is there a way to have the dept auto enter based on a code I put in or do I have to manual type this in all the time when I create the record in my Kiosk table? For example, if I type in SH for the code, it automatically puts in "Shoes" for the department. Not sure if this can be done, but was curious.

                              • 12. Re: Calculation for text fields

                                     Kiosk Qty is in the wrong table. It has to be in store. Remember you're counting the children records. Count(), used this way is counting all the children.

                                     My example "Count ( Kiosks::Kiosk_ID )"  is showing the relationship "Kiosks::". This means it's not the current table but a related one.

                                     I think you need to find some books on basic Relational Database design. It will help you get all this straight in your head.  I've been doing this so long I forget how confusing it was at first.

                                     I just googled this one and it looks pretty good.