1 (edited by papafrankc 2020-07-30 04:57:09)

Topic: Calculated Field Problem

I'm having trouble with a calculated field
-
I have a Users table with FirstName, LastName....etc.
-
I created a Calculated field (FullName) with the following code:
tblUsers.FirstName || " " || tblUsers.LastName 
-
I also tried using this code from one of the sample applications:
ifnull(firstname,'')||' '||ifnull(lastname,'')
-
However when I try to use FullName on my forms, it only works on some forms, but not others
-
On the forms where it doesn't work I get an error message saying that tblUsers.FirstName doesn't exist.
-
I configured everything the same on all forms, and as I said it works on some but not all.
-
I can put the FirstName and LastName in separate text boxes, but it doesn't look very good with extra spacing between the 2 names.
-
Maybe there's a better way to get these names together on a form?
-
Any thoughts will be appreciated.
-
Thanks, Frank

Re: Calculated Field Problem

Hi Frank,
Can attach your project, otherwise we're shooting in the dark.
Thanks,
Derek.

3 (edited by papafrankc 2020-07-31 01:00:17)

Re: Calculated Field Problem

Derek,

Here's a copy of my project.
-
The calculated field FULLNAME works OK in the MainMenu but when trying to go to CONTACTS and DOCUMENTS, from the MainMenu that's when I get the error.
-
Thanks for looking into this for me.
-
I had to create the ZIP file without the sqlite.db file.  It kept saying the file was too large?  I haven't done much zipping before so I guess this is normal?  Although I think I've seen zip files from MVD that had data in them.  Anyway let me know if I need to do anything different
-
Frank

Post's attachments

Attachment icon RECORDS-TEST.zip 351.85 kb, 212 downloads since 2020-07-31 

Re: Calculated Field Problem

Hi Frank,
Can you check the attachment - nothing showing at the moment.
Derek.

Re: Calculated Field Problem

I just re-attached the Zip file.  It should be OK now.
Thanks

6 (edited by derek 2020-07-31 11:34:05)

Re: Calculated Field Problem

Hi Frank,
The full name doesn't show because your calculated field is defined against tblusers.  All you need to do is create a 'fullname' calculated field on the other tables (tblcontacts and tbldocuments);  the syntax is a bit different because you need to link across to tblusers to get the information.  Have a look at records-test 1 in the attachment to see how it's done.
There are a couple of other things you should be aware of. 
In records-test 1 on your 'start' form, you have 2 edit buttons (one to edit the user and one to go to 'frmmainmenu') and this can cause an inconsistency.  If you select a user in the start form and then click the 'edit' button to call 'frmusers', when you close 'frmusers' you return to the 'start' form.  However, if you doubleclick on a row in the table, when you then close 'frmusers' it takes you to 'frmmainmenu'.  I'd suggest removing the 'edit' button so that users can only be 'added' or 'deleted' from the 'start' form and do any editing of the user from the 'users' tabsheet on 'frmmainmenu'.  Have a look at records-test 2 in the attachment where the action should now be consistent.
More generally, I wonder if you really need to have the 'fullname' at any level other than tbluser - in records-test 2, as a suggestion I've simply changed the layout of 'frmcontacts' and 'frmdocuments' so that they no longer obscure the 'fullname' that is already displayed - the placement of the forms makes the fullname that they relate to much more obvious.
Another way of tackling it is shown in records-test 3.  Here I've added a small script just to give a feel for options you might want to consider further down the line - in this example, instead of the caption simply showing 'frmcontacts', it now shows 'Contacts for - (and then the users full name).
I guess what I'm basically saying is that being aware of some of the different options has an impact on your processing logic and your form layout (for example, using the form caption to hold the user fullname takes up much less form real estate).  But I appreciate it's all very much a matter of taste.
Hope all of the above has helped and not hindered - LOL!!
Derek.

Post's attachments

Attachment icon frank records.zip 1 mb, 225 downloads since 2020-07-31 

7 (edited by papafrankc 2020-07-31 18:37:16)

Re: Calculated Field Problem

Derek, many many thanks.
-
I believe I'll go with records-test 2.  It appears to be the simplest (for a newbie like me)
-
Your idea to make the sub-forms  (contacts & documents) smaller and positioned below the FullName on the MainMenu is simple and gets the job done.  Doing it this way insures that the person using the program will always be able to see which User they are adding or changing records for.
-
Records-test 3 is great also.  But for me, for now, I want to keep things as simple as possible since I'm still learning.  Once I get the basic program working, then I'll look into adding some more bells and whistles. 
-
I'd like to make a suggestion on a separate issue.  Since I've been on the Forum for a while now, I've noticed that there is a wealth of knowledge from yourself and others.  It would be nice if that knowledge could be put into a video tutorial.  I know time is money, so maybe you could make a deal with Drivesoft to offer the video to only registered users of MVD.  This way you could be compensated for your knowledge and efforts, and Drivesoft would have an enticement for new users to purchase MVD.  I know, for myself, when I'm learning something new, I always look for video tutorials that are well done.
-
********** When I first posted this reply I hadn't noticed that one of the STICKY'S on the general page lists a number of tutorials.  I'm going to check them out, I'm sure there is a lot of info that will be helpful to myself and others.
**********
Again, many thanks for all the help you give us MVD users smile
Frank

8 (edited by papafrankc 2020-08-01 19:42:25)

Re: Calculated Field Problem

Derek,
One question: The CONTACTS and DOCUMENTS forms  (sub-forms as I call them)  open up below the top of the MAINMENU form and that's good since the MAINMENU form shows the Users FullName.
-
However the current CONTACTS and DOCUMENTS forms are pretty small with only a few fields.  My finished product will have a lot more fields on most of  the sub-forms.
-
When I make the sub-forms bigger they open up in the center of the screen and cover up the Users FullName.
-
I've been playing around with POSITION of the form but nothing seems to let me position the sub-forms where I would like.
-
Is there any way to make the sub-forms bigger but have them open up so they don't cover up the top of the MainMenu form?
-
UPDATE Going back over the 3 examples you included I think Example 1, with a Calulated field in each one of my tables will be the best way to go.  Doing it this way I can put the FULLNAME field on any form and in any position. And then I don't have to worry about positioning the forms.
-
I also like the layout of Example #2 so I'll just add the Calculated fields to the tables in this layout.
-
Sounds good to me unless you have any other suggestions?
-
Thanks, Frank

9 (edited by derek 2020-08-01 23:04:25)

Re: Calculated Field Problem

Hi,
The position of subordinate forms can be placed wherever you want them but this has to be done by script;   by default, they are centred on the main form.
If a larger subordinate form is obscuring the 'fullname' then using a calculated field for the 'fullname' in each of the relevant tables as you suggest is a workable solution.  The other way you might approach it is to increase the size of the main form to the point that the 'fullname' is visible again.
Derek.

Re: Calculated Field Problem

Thanks Derek,
So far the calculated field on each form is working OK.  It lets me arrange the forms pretty much as I want.
-
When I first saw your expression for the calculated field it seemed a little much for my skill level, but actually I found it is pretty easy to modify and implement for my other tables.  I learn something new pretty much every day and that's a good thing.
-
Thanks again, Frank