Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Calculated field derived from another calculated field

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Calculated field derived from another calculated field

    I'm trying to export a calculated field which is derived from another calculated field. I've created a mapped table which includes the first calculated field, but when I try to use the first field as part of the definition of the second calculated field, I get the message "Display expression is not valid". I would be very glad to hear how it might be done.

    Thanks
    Mike

    #2
    Re: Calculated field derived from another calculated field

    One approach to something like this avoids nesting calc expressions within each other. Just write the final calc expression without using the previous. i.e. write the final expression without referencing the previous calc value, but re-using the previous expression, so that it gets computed all over again.

    Comment


      #3
      Re: Calculated field derived from another calculated field

      Thanks Tom. I had wondered about that approach but was hoping to avoid it because it will make the expression very long. However, if there is no other way, it will have to be.

      Mike

      Comment


        #4
        Re: Calculated field derived from another calculated field

        Mike,

        Just for conception. * Search previous messages by "Nesting "
        (1) Call the 3th value by UDF. or
        (2) Review the Set structure

        Can you provide some of detail?

        Comment


          #5
          Re: Calculated field derived from another calculated field

          John
          Thank you for your reply. However, I would be grateful for a little help in understanding what you mean by items (1) and (2).
          To add more detail about the problem:
          I need to export a number of fields including several that are calculated. As long as they are not nested, I can create them in a mapped table. The trouble arises when I try to create a calculated field which depends on a previously calculated field. The option 'F5 Calculated Field' doesn't appear in the Expression Builder for a table in the way it does for a Layout.
          For instance, one of my expressions is : sum_of_fees = calc->Feepmtdd + calc->Eurfee + calc->Sum_Ecfee + calc->Manfee + calc->Cenv_Fee
          All five components are defined in the mapped table, but the sum of them just gives "Display expression is not valid".

          I appreciate that I could avoid using nested calculated fields by expressing everything fully, but some of them would be very long and not easy to maintain. Also, I wondered if there was some way to export the data from a layout but haven't had any success.

          Any advice would be welcome.

          Mike

          Comment


            #6
            Re: Calculated field derived from another calculated field

            Mike,

            Give me the sample DB here or send me private message.

            Comment


              #7
              Re: Calculated field derived from another calculated field

              John
              Here is a zip of the whole workspace. I've put most of the calculated fields in the set of tables called 'full-set' and used some of them in the layout called 'members'. In that layout, if you look for example at the calculated field 'feepmtdd' (in the beige panel at the bottom right) you can see that it successfully uses another calculated field, instfee (and the Expression Builder includes Calculated Fields F5). If I try and create 'feepmtdd' as a calculated field in the table used for export ('mapped table for export and reports') it will not allow nested calculated fields and the Expression Builder does not include Calculated Fields F5. That is the problem. I hope there is a way round it without having to expand all the nested definitions.

              I have had to empty all the tables of data as they contained personal information about the members.

              Thanks for your help. Much appreciated.

              Mike
              Attached Files

              Comment


                #8
                Re: Calculated field derived from another calculated field

                I put this in so even casual browsers can see.

                Here is some code used by about 500 people on a daily basis. It has worrked without touch for many years and still does today. Not the references to calc fields from within calc fields:


                Code:
                tradetotallien = total(Trades->Lamt)
                tradedisc = if(calc->tradetotallien>=.01,"CUSTOMER ACKNOWLEDGES THAT THE PAYOFF BALANCE IS ESTIMATED."+(chr(13)+chr(10))+"IF ACTUAL PAYOFF IS HIGHER, THE CUSTOMER AGREES TO PAY DIFFERENCE."+(chr(13)+chr(10))+"IF PAYOFF  IS LOWER, THE CUSTOMER WILL BE REIMBURSED.","")
                tradetotalallow = total(Trades->Tradeamount)
                cstatetax = if(Mon->alttax>=0.01,Mon->alttax,if((((mon->msrp+mon->add1+mon->add2+mon->add3+mon->add4+mon->add5+mon->filingfee+mon->bat+mon->tirebat+Mon->electron+Mon->antitheft)-(mon->discount+total(Trades->Tradeamount)))*(Mon->taxrate/100))<=0.00,0.00,(((mon->msrp+mon->add1+mon->add2+mon->add3+mon->add4+mon->add5+mon->filingfee+mon->bat+mon->tirebat+Mon->electron+Mon->antitheft)-(mon->discount+total(Trades->Tradeamount)))*(Mon->taxrate/100))))
                tradetotalcost = total(Trades->Cost)
                tradeleft = Trades->Tradeamount-Trades->Lamt
                tradeinfo = "Total Liens: "+trim(str(calc->Tradetotallien,10,2))+"   Total Allowance:   "+trim(str(calc->Tradetotalallow,10,2))
                datesold = if(isblank("Deliverdate"),date(),Deliverdate)
                newwheels = if(Mon->newwheels<=0.00,"======",str(Mon->newwheels,8,2))
                neworused = if(Inv->Noru="N","New",if(Inv->Noru="U","Used",if(Inv->Noru="D","Demo","Prev Lease")))
                names = trim(Fname)+" "+trim(Mname)+" "+Buyer
                namesc = trim(cFname)+" "+trim(cMname)+" "+co_Buyer
                gmsapprove = If(Gms="T","GMS Authorization: "+trim(Gmsauthorization)+" Approval:  "+Gmsapproval,"")
                gmcard = if(Mon->gmcardamt>0.00,"GM Card OK: "+alltrim(mon->Gmcardok)+if(.not.isblank("Mon->Gmcardcode")," Code: ","")+alltrim(mon->Gmcardcode)," ")
                salesman = trim(Var->Usernow)+" "+if(.not.isblank("Splitwith"),"/  ","")+Splitwith
                inscomp = "Agent "+trim(Agent)+"  Address "+trim(Agaddress)+";"+trim(Agcity)+", "+Agstate+", "+Agzip+"  Phone "+Agphone+"  Ins Co.: "+trim(Insco)+"  Policy: "+Policy
                diff = ((Mon->msrp-mon->discount)+mon->add1+mon->add2+mon->add3+mon->add4+mon->add5+Mon->Antitheft)-calc->Tradetotalallow
                liento = if(Trades->Lamt>=1.0,if(isblank("Trades->Liento"),"","Lien To: "+trim(Trades->Liento))+if(isblank("Trades->Laddress"),"","  Add: "+trim(Trades->Laddress)+"; "+trim(Trades->Lcity)+", "+Trades->Lst+" "+trim(Trades->Lzip))+if(isblank("Trades->Lphone"),"","  Phone: "+Trades->Lphone)+if(isblank("Trades->Goodtil"),"","  GoodTil: "+Trades->Goodtil)+if(isblank("Trades->Lacct"),"","  Acct: "+Trades->Lacct)," ")
                csoldprice = (Mon->msrp-Mon->discount)+mon->add1+mon->add2+mon->add3+mon->add4+mon->add5+mon->antitheft
                auto1 = "Enter My Order as Follows: "+calc->Neworused+" Stock#  "+trim(Inv->Stock)+(chr(13)+chr(10))+" Yr: "+trim(Inv->Year)+" Make: "+trim(Inv->Make)+" Mod: "+trim(Inv->Model)+(chr(13)+chr(10))+" Trim: "+trim(Inv->Trim)+" Bod: "+trim(Inv->Body)+" Col: "+TRIM(Inv->Color)+" "+trim(Inv->Cyl)+" Cyl"+(chr(13)+chr(10))+"Vin# "+alltrim(Inv->Vin)+"     Miles: "+alltrim(Inv->Miles)
                autotrades1 = "#"+trim(str(Trades->Tradenumb,2))+" -Stock# "+trim(Trades->Stock)+" "+trim(Trades->Year)+" "+trim(Trades->Make)+" "+trim(Trades->Model)+"  "+trim(Trades->Trim)+" "+trim(Trades->Body)+" "+TRIM(Trades->Color)+" VIN: "+trim(Trades->Vin)+"  Miles "+trim(Trades->Miles)
                ctaxable = (mon->msrp+mon->add1+mon->add2+mon->add3+mon->add4+mon->add5+mon->filingfee+mon->bat+mon->tirebat+Mon->electron+Mon->antitheft)-(mon->discount+calc->Tradetotalallow)
                addressB = (if(isblank("Pobox")," "," (P.O.Box "+trim(Pobox)+")"))+trim(Stnumb)+" "+trim(Address)+" "+trim(City)+" "+trim(County)+" "+trim(state)+" "+left(Zip,5)
                addressC = trim(Cstnumber)+" "+trim(Caddress)+" "+trim(Ccity)+" "+trim(County)+" "+trim(Cstate)+" "+left(Czip,5)
                disctax = if(mon->Altdisctax>=.01,mon->Altdisctax,dtaxget(calc->Ctaxable,mon->Disctaxrate,mon->Discmax))
                tax = if(calc->Cstatetax+calc->Disctax<=0,((Mon->Bat+Mon->Tirebat+Mon->Antitheft+Mon->Filingfee+Mon->Electron+Mon->Filingfee)*Mon->Taxrate)/100,calc->Cstatetax+calc->Disctax)
                clefttotal = Mon->Msrp+calc->Disctax+calc->Cstatetax+Mon->Add1+Mon->Add2+Mon->Add4+Mon->Add3+Mon->Add5+Mon->Newwheels+Mon->Bat+Mon->Tirebat+Mon->Filingfee+mon->lemon+mon->tags+mon->electron+mon->antitheft-mon->discount
                mstatetax = if(if(mon->taxrate<12,(((calc->Csoldprice+mon->bat+mon->tirebat+mon->filingfee+mon->antitheft+Mon->electron)-(calc->Tradetotalallow))*(Mon->taxrate/100))+calc->Disctax,mon->taxrate)<=0,0,if(mon->taxrate<12,(((calc->Csoldprice+mon->bat+mon->tirebat+mon->filingfee+mon->antitheft+Mon->electron)-(calc->Tradetotalallow))*(Mon->taxrate/100))+calc->Disctax,mon->taxrate))
                botright = calc->Clefttotal+calc->Tradetotallien+Mon->Warranty+Mon->Wartax-(calc->Tradetotalallow+Mon->Reb1+Mon->Reb2+Mon->Reb3+Mon->Reb4+Mon->Deposit+Mon->Cashondel+Mon->Gmcardamt)
                Dave Mason
                [email protected]
                Skype is dave.mason46

                Comment


                  #9
                  Re: Calculated field derived from another calculated field

                  Mike,

                  How can I open the Control panel?

                  Never mind, I used the Key
                  Last edited by johnkoh; 06-13-2013, 12:52 PM.

                  Comment


                    #10
                    Re: Calculated field derived from another calculated field

                    Thanks Dave. Is your code used on a layout or in a table? I'm glad to say that I'm not having any trouble with nested calculated fields used in a layout. The problems arise when I try to create a nested calculated field in a mapped table. The reason I need a mapped table is to export the nested calculated fields. There must be a way.

                    Mike

                    Comment


                      #11
                      Re: Calculated field derived from another calculated field

                      John
                      Hold down Ctrl+Shift and open the workspace. Then check the box 'Do not open the startup form'.

                      Mike

                      Comment


                        #12
                        Re: Calculated field derived from another calculated field

                        I think I'm seeing the issue in a different way.

                        In the global calculated fields for the ful set.set (I don't recommend spaces in table or set names, but meanwhile) you have defined instfee without reference to any calculated field and feepmtdd based on calc->instfee, but the expression builder says feepmtdd is an invalid expression. This is while the members form is not open. If you open the members form, go back and look at the set global calculated fields again and feepmtdd is now valid and yields a result.

                        If you make instfee and feepmtdd form level calculated fields they also work.

                        What I think is happening is that you can't have set level global calculated fields that refer to other set level global calculated fields.
                        There can be only one.

                        Comment


                          #13
                          Re: Calculated field derived from another calculated field

                          Used in a report although I have similar in forms and other.

                          Originally posted by trackbed View Post
                          Thanks Dave. Is your code used on a layout or in a table? I'm glad to say that I'm not having any trouble with nested calculated fields used in a layout. The problems arise when I try to create a nested calculated field in a mapped table. The reason I need a mapped table is to export the nested calculated fields. There must be a way.

                          Mike
                          along with what stan said: look here: http://msgboard.alphasoftware.com/al...cation-warning
                          and: http://msgboard.alphasoftware.com/al...runcated+field

                          and I have seen tables get truncated to 8.3 and fields to 10 inside a table. I do not use spaces for any name anywhere form past experience. Those experiences are not fun on the very rare occassion they pop up.
                          Last edited by DaveM; 06-13-2013, 01:23 PM.
                          Dave Mason
                          [email protected]
                          Skype is dave.mason46

                          Comment


                            #14
                            Re: Calculated field derived from another calculated field

                            Thanks Dave and Stan. I note the warning about the table and field name lengths.
                            I've tried creating the nested calculated field with the form open as Stan suggests, and sure enough it works. I'll be back on to it tomorrow.

                            Comment


                              #15
                              Re: Calculated field derived from another calculated field

                              Mike,

                              I am still work with your sample...

                              Suggestion -- Review the table normalization and normalization. It is most important matter for using any DB.
                              It will makes a lot easier to manipulate with tables, easy to understand the structure and write the simple code later.

                              Comment

                              Working...
                              X