How to autopopulate data in confiforms

[Shubham Garg]

I have built a confiform and now I want to autopopulate data in some field by using my database by seeing which user is logged in for e.g. , I would not want the user to enter his name and instead get his name by using the current logged in user. Please tell me how to go forward with this.

[Davin Studer · Accepted solution]

In the ConfiForms Registration Control macro there is a field for setting default values. See the below screenshot.

image2016-6-14 11:54:27.png

[Shubham Garg]

Thanks It works this way.

But if I try to use  "user.fullName" with default value by making my Name field as Readonly . This does not work.

And also where did you get this command user.fullName . Can you provide me a link to its documentation.

Thanks

[Davin Studer]

Readonly should work. I have a form with a field called lastEditedBy that is a readonly and it populates the current user when the entry is created or edited. The ConfiForms documentation link is below. It is on that page. Do a page search for [user.fullName] and you should find it. Second link is also a good one for knowing how to access a field and its properties.

https://wiki.vertuna.com/display/CONFIFORMS/Documentation

https://wiki.vertuna.com/display/CONFIFORMS/Accessing+field+values+and+properties

[Randall Hinds]

@Davin Studer Is there any way you might be able to repost the screenshots from the Answer Accepted?

I have just been able to connect our Confluence page to our PostgreSQL DB, and I can set Field Definitions to query individual fields successfully. 

Using the Dynamic Dropdown (database) field type, I'd like to query a column by a unique ID and then populate other fields from other DB columns in that same row.

[Alex Medved _ConfiForms_]

@Randall Hinds 

What is your field name for the dynamic dropdown field and what are other fields that you query in the SQL for this field?

The logic (to access field's other fields/properties) is as follows

mydynamic_field.field_in_the_sql

Alex

[Randall Hinds]

I've named my dynamic dropdown field BIDNumber , and it successfully queries the first column in this SQL:

Select brdg_id from eccdata.brdg_dtl
ORDER BY brdg_id ASC

These are all the fields in that particular table

brdg_id incdnt_no_tx brdg_type_id creat_user_id creat_ts lst_updt_user_id lst_updt_ts act_in ecc_init_brdg_in brdg_init_by_user_id miss_rsn_expl_tx miss_rsn_val_id proact_trgr_type_val_id slack_chan_thread_url_tx ecc_esclt_brdg_in slack_appl_esclt_ts slack_mjr_incdnt_mgmt_esclt_ts ecc_alrt_ntfy_ts triage_team_incdnt_no crcmvnt_ds prob_stmt_tx pstmrtm_rev_in prob_task_no_url_tx key_takeaway_tx ecc_iss_detct_ts suc_story_elig_in suc_story_tx prob_task_suc_in prob_task_not_creat_rsn_id brdg_creat_tz_val_id dashboard_slct_in full_stack_vw_useful_in full_stack_vw_useful_cmnt_tx mim_brdg_strt_ts mim_portal_updt_in brdg_sub_type_id brdg_tier_type_id hub_update_in brdg_sta_type_id mim_brdg_end_ts da_ops_usefulness_cmnt_tx da_ops_usefulness_in da_ops_usefulness_root_cause_in metrics_avail_in_fso_dashboard_in metrics_avail_in_fso_dashboard_cmnt_tx

So... if I understand correctly, when a user selects BIDNumber from dropdown (pulling brdg_id ), I can display other detail from the same row with something like :

BIDNumber.incdnt_no_tx

My initial goal is to build reports within Confluence pulling from our PostgreSQL  DB backend, and there will be several tables of data and fairly granular conditions to filter data/detail against.  
If/when I can get that kind of logic working to display in a report, I want to start working to use Confluence to update data in some fields and maybe even create new rows of data in certain tables.  
--> Are there any guides or examples I might be able to learn from?

[Alex Medved _ConfiForms_]

Sorry, I dont think ConfiForms is a right tool to build the reports from your database data.

ConfiForms views col dshow you only the data that is stored within ConfiForms dataset. True that - this data might initially come from a different sources, including the database

Little demo: http://recordit.co/5fvqw2qkgt

Some rules on working with DB fields in ConfiForms - https://wiki.vertuna.com/display/CONFIFORMS/Working+with+database+fields+in+ConfiForms

Alex

[Randall Hinds]

Thank you for the video.  It helped me grab additional field from the table and specific row of the field I am querying.

I completely agree with you here --> "dont think ConfiForms is a right tool "
Unfortunately, it is the best tool I have at my disposal.  We have a .NET Core based web frontend, and the majority of my dataset I exported from PG DB via search pages on our intranet site.  Our dev team is short staffed and swamped with higher priority work. 

Meanwhile, we've been using Confluence to build report pages manually with straight copy/paste exercises crunching counts & stats in Excel. 
To reduce our manual reporting efforts, I've had good success building ConfiForms reports with form table dataset entered directly & imported from files.  

I was pretty sure I could use the dataset by pulling from the Working with DB fields in ConfiForms page you referenced, and this statement "Of course you can re-use your database connections, have multiple DB fields on the same form, etc. Fields behave and act as normal ConfiForms fields" was very encouraging.  

Whether ConfiForms is the right tool or not, I have to try automating as much of our reporting as possible.  I will keep forging ahead grabbing from DB into a data table.

Thanks for your guidance getting me this far, --> R

[Fred Bunting]

The answer depends on your goal for wanting to auto-populate the field.

If the goal is to auto-populate the field so that the user cannot override the value (a read-only field), then define the field as either a Calculated field-type, or a Formula.   The difference is that a Calculated field is populated once at registration time, and a Formula is re-calculated every time the user updates the record.

If the goal is that the field is auto-populated with a default value, which the user can then override, then yes, Davin's answer is correct -- you can configure the Registration Controls macro to pre-populate one or multiple fields at registration time.  However, if you display the Registration Controls in several places (e.g. on different pages), then you need to remember to configure the Registration Controls each time.

An alternative is to add a Field Definition Rule in the form itself. I believe the trick is to give it the Condition id:[empty], which only executes at registration time (because that's the only time the id is empty).  The advantage here is that this is defined in the form itself, rather than in the Registration Controls, which may be one less thing to worry about if you're putting Registration Controls in several places (like different pages). 

These last two alternatives are explained on https://wiki.vertuna.com/display/TEST/Form+with+default+values+set+by+Field+def+rules+and+by+registration+control

Finally, if your goal is to auto-populate a field for multiple records that have already been created -- e.g. if you add a field later, after people have already entered data -- then you need to run the REST API update.action to batch-update the field on multiple records.  This is necessary even for Calculated or Formula fields, as those only get calculated at registration time, or when the user updates the record.  The caveat I would add is to test this on test data first.  I've found that the REST API is very reliable, but if you make a mistake in the syntax, you could end up overriding some data you want to keep.

The REST API is described on https://wiki.vertuna.com/display/CONFIFORMS/REST+API

[Davin Studer]

If you are using ConfiForms then you are not using Confluence Cloud, so your question should not be tagged as such.