Get data from one table to another table in a form

[andemosa]

I have a Drug Table that has a selling price field.

I have a Sales Table with a quantity and Total field. It is also referencing the drug table via relationships.

I am trying to create a function that triggers when i enter the Total field on the Sales form. I want this function to use the selling price of the selected drug to multiply the quantity so as to get the total amount. How do I achieve that?

[Jo-Anne]

Hi andemosa,

You can add a lookup query to the Sales form to have more data available from the Drug table and then use the getMetadata() function to calculate the total. I have provided the steps for you below.

I have created a similar setup to you and have a Drug and Sales table and a Drug and Sales form.

Add a Drug Lookup Query

This query will provide metadata to the Sales form through the DrugKey field.

1. Select Visual in the Menu.

2. Select Queries in the sub-menu.

3. Click the Add Item button.

4. Type Drug Lookup in the Title field.

5. Click in the Query field to open the Query Builder.

6. Click the SQL tab.

7. Type the following:

SELECT
DrugKey,
Name,
SellingPrice
FROM
Drug

8. Click the Save button.

9. Click the Fields tab. Note: You need to click the Fields tab to populate your query fields before saving the query record!

10. Select the SellingPrice record.

11. Either click the Edit button or click directly in the Display Type field.

12. Click the lookup icon in the Display Type field and select _Float.2.

13. Click the Save button in the form app bar.

14. Click the Save button in the form app bar above the list.

Attach the Drug Lookup Query

Instead of the DrugKey field on the Sales form just being a straight lookup, we can edit this and make it have a display type of _LookupQuery and attach the Drug Lookup query to it, this will then contain the metadata (SellingPrice) on the DrugKey field.

1. Click Visual in the menu.

2. Click Forms in the sub-menu.

3. Select your Sales form record.

4. Click the Pages tab.

5. Select the Page record holding the DrugKey field.

6. Click the Fields tab.

7. Select the DrugKey record.

8. Either click the Edit button in the form app bar or click directly in the Reference Form field.

9. Click the Cancel button in the Reference Form field.

10. Click the lookup icon in the Display Type field and select _LookupQuery.

11. Click the lookup icon in the Lookup Query field and select DrugLookup.

12. Click the Save button in the form app bar.

13. Click the Save button in the form app bar above the list.

Add the CalculateTotal Function

The CalculateTotal function will now have access to the SellingPrice field through the Drug Lookup query.

1. Select Logic in the menu.

2. Select Code Editor in the sub-menu.

3. Click the Add New Code button.

4. Type CalculateTotal in the Function ID field.

5. Click the lookup icon in the Language field and select JavaScript.

6. Click the OKAY button.

7. Type the following in the body of the function:

   five.field.Total = five.field.Quantity * five.getMetadata(‘Sales’, ‘DrugKey’, ‘SellingPrice’);

8. Click the Save Current Tab button.

Note: getMetadata(FormID.FieldID.QueryFieldID)

Please refer to the following documentation and examples to have a thorough understanding of the function getMetadata().

getMetadata() | Five | Low-Code For Real Developers

getMetadata() | Five | Low-Code For Real Developers

Attach the CalculateTotal Function to the On Enter Event

The Sales form will need to be edited again so that you can now attach the CalculateTotal function to the Total field on the Sales form.

1. Select Visual in the menu.

2. Select Forms in the sub-menu.

3. Select your Sales record in the list.

4. Click the Pages tab.

5. Select the page record holding the Total field.

6. Click the Fields tab.

7. Select the Total record.

8. Click the Events tab.

9. Either click the Edit button or click directly in the On Enter event.

10. Click the lookup icon in the On Enter field and select CalculateTotal.

11. Click the Save button in the form app bar.

12. Click the Save button in the form app bar above the list.

You can run your application now and once you have added a drug record like below.

You can then add a Sales record and the Total field will automatically calculate on entering the Total field.

[andemosa]

Thanks a lot Jo-Anne. This really helped