Macro Help

Brian Gosnell asked on February 17, 2021 03:27

I am having some difficulties with formatting a macro that sets an online form field default value from a selected dropdown list in the form that then reads from a custom table.

{% GlobalObjects.CustomTables["customtable.Hospital"].Items.Where("ItemID = '{% HospitalName.Value %' + '}'")[0]["Hosp_Address"] %}

Hospital name in the where clause is the name of the dropdown list in the form. Hosp_Address is the field in the custom table I want to pass as the default value. I have set the has field dependencies for the drop down and the Field where the macro is.

The error I get is

Caused exception: Conversion failed when converting the varchar value '{% HospitalName.Value %}' to data type int.

Hopefully someone can help

Recent Answers

Juraj Ondrus answered on February 17, 2021 09:43

Where is the macro set exactly? Also, the ItemID is probably some ID, right? Why are you assigning a string to it (using the apostrophes) - the HospitalName field name does not sound it holds an integer value and you are also using the + sign to contcatenate strings?

0 votesVote for this answer Mark as a Correct answer

Brian Gosnell answered on February 17, 2021 16:10


The Macro is being set in the default value for the address field. ItemID is the Key field for the custom table . HospitalName field is a drop down with id as value and Hospital name as name. I concatennated the } because when I didnt it ended the macro there instead of showing the rest.

0 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on February 18, 2021 06:55

Thanks for the details. In the WHERE clause, you use standard SQL syntax. So, if you are comparing an integer value, do not use apostrophes, which are for strings. There should be just like: "ItemID = 5" - where the 5 should be the result of the inner expression. So, the macro should look like this:
{%GlobalObjects.CustomTables["customtable.yourtable"].Items.Where("ItemID =" + Field.Value).FirstItem.ReturnValueFieldName#%}

However, the issue here is that the macro in default value is evaluated only once, when the field is rendered, due to performance. So, if you change the value in the other field, it has no effect. In this case it would be better to create a custom form control (portal engine) or form component (MVC/Core development model) which will consists of two fields and you will handle the logic in the code and dynamically load the values as you need. Or, if you will use it as a drop down list and use the macro to fill out the list or, maybe just as a label.

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.