Hello Support,
I have two related tables – Table A and Table B.
Table A has two fields – Field 1 and Field 2.
Table B also has two fields – Field 10 and Field 20.
I have a Form sourced from Table A. The Field 2 fields of Table A on the Form has to be calculated by formula: Field 2 = (Field 1 * Field 10) / Field 20.
How to do it on the Form step-by-step?
I believe many people would be happy to know the answer to this question.
THANK YOU!
Vlad T.
Hi @vladt
To assist you better, I need more details about your database and the structure of your forms within your app. If you could export and send me the FDF of your application, that would help me in getting a better idea. You can send the FDF here or email it to me at pranoy@five.co.
Here is a link to our documentation that can help you export the FDF: Exporting and Importing FDF.
Hello Vladt,
Attached is an FDF containing the set up you have described as well as the functionality to produce the calculation you are requesting. To do this calculation, you must use an SQL query and a JavaScript function.
The process is essentially as follows
1. In form A, select the record from table B to be used for the calculation.
The above image contains the SQL query used to retrieve the required values from Table B for the calculation to be made using the record selected within form A.
This SQL query is attached to the ‘Table B Key’ field with form A, allowing it to use the value select within this field to complete the query.
2. In form A, input the value to be used in the calculation (Field 1)
This field has a JavaScript function attached to the ‘OnExit’ event.
This JavaScript function makes the calculation after a value has been entered into ‘Field1’ on Form A.
It uses the getMetadata function on the Five object in conjunction with the previously attached SQL query to acquire the values from table B. For information regarding the syntax of the getMetadata function, refer to getMetadata() | Five | Low-Code For Real Developers
Step 3. After entering the value to be used in the calculation within Form A (field 1), click out of the text box and Field 2 will be filled with the result of the calculation.
Note: Since you are currently using a free trial, you will only be able to have one application within the cloud version at a time, however, you can have multiple within the local version. The location version can be downloaded via Download | Five
Feel free to ask any questions, more than happy to provide further information,
Riley.
CalculatedFields-20240729-050721212736278.fdf (3.4 MB)
Hi Riley,
Thank you for the detailed reply. I downloaded the .fdf file you provided and imported it to the standalone version of FIVE. Unfortunately, when I tried to run the app, it gave me an error (see screenshots attached).
Nevertheless, I’ll try to replicate the method you described and will let you know how it goes.
Regards,
Vlad T.
Hello Vladt,
I did the sample app in our upcoming release, 2.7. The version you are currently using is 2.6. We have had a change where on a form field in 2.6, you have a switch to include a field in the list, in 2.7, we have changed this to a conditional field.
All you need to do is go into (using the same structure in the fdf I sent):
Visual > Forms > Click TableAs > Pages > General > Fields > Field1 > Adjust the switch under ‘Include in list’ > repeat for TableAs Field 2, TableBs Field10, and TableBs Field 20.
The image above displays the switch.
Let me know if you need any extra help, can provide a more detailed walkthrough if needed!
Thanks,
Riley.
Thanks for the heads-up, Riley! When will the new release be available for download and what else is new in it (if you may say)?
Hi Vlad, it shouldn’t take too much longer for our new release to come out. We will publish a feature overview on our website and we’ll make sure to give it to you when it’s ready! 
