InfoPath 2010

Re: Auto-populate Pre-determined Text if field is blank

Hilary Stoupa — Tue, 03 Jan 2017 00:22:14 GMT

So this is something you are trying to do in your SharePoint list as a calculated column? InfoPath doesn't have IF or ISBLANK. I'm attaching a simple sample that has two tables bound to the same data source. Save the file locally, preview and in the top instance of the table, enter some data. Leave the data column blank in some rows & enter a date in others. You'll see that I've used conditionally formatted calculated values in the second instance of the table to display either the entered date or some text. Hopefully this will help.

EDITED TO ADD: Save the linked file locally, right click and select Design to open in design mode. From there you can preview, look at the design, etc.

Re: Auto-populate Pre-determined Text if field is blank

Curtis — Tue, 03 Jan 2017 00:13:07 GMT

Understood. I've tried several version of a formula in the field I want to show the date of inspection or No Report Submitted. Last version was, no luck.

=IF(ISBLANK([Date Previous Inspection])," ",No Report Submitted"))

Re: Auto-populate Pre-determined Text if field is blank

Hilary Stoupa — Mon, 02 Jan 2017 23:28:59 GMT

My instructions are intentionally vague, as I obviously have no details as to the actual structure of your form. Which of your two secondary data connections has the data where you want to reset the field to display "No Report Submitted"? It would probably be way easier to just put a calculated value in the area where you want to see this text and use conditional formatting to determine which you want to show. Attach the template to a reply under options if you need a hand with that.

Re: Auto-populate Pre-determined Text if field is blank

Curtis — Mon, 02 Jan 2017 23:22:19 GMT

Can't get this to work.

There are two data connections; Master Bldg List and Master System Inspection List

The form, including the repeating table connected to the Master System Inspection List, auto-populates once the Building No. is entered.

Your original suggestion was to , "repurpose" another field in the secondary data connection - I'm assuming that means the Master System Inspection List connection.

On the Building No. field Rule 1 queries the Master Bldg List data connection for the Building No.; Rule 2 queries the Master Bldg Connection List for the rest of the form data.

There is a repeating table that includes several fields from the Master System Inspection List. There is a rule on this table that fires the Master System Inspection Data Connection once a valid Building No. is present.

The Master System Inspection Building No. must match the Master Bldg List Building No. for data to appear in the repeating table.

Your instruction was to "... put a rule on the ID field to set the date field based on a condition..."

I've attempted to make a Rule on the 'ID' field of the repeating table Master System Inspection Connection with a condition that would make the 'Date of Last Inspection' field "No Reported Submitted", based on the Master Bldg List data connection

Your instruction continued with "...on the building number, set ID (after querying the data source) to something it will never normally be, like 0."

In Rule 4 of the Master Bldg List data connection I've set the value of the 'ID' field to "0".

The Date of Last Inspection field in the repeating table still appears blank where no data has been entered into the Master System Inspection List

Re: Auto-populate Pre-determined Text if field is blank

Curtis — Mon, 02 Jan 2017 20:56:22 GMT

One follow-up; one of the fields impacted is a 'Date Next Due', that is set as a calculated value, date type, date only, field. The text in this field appears in my InfoPath form field as 2018-03-10 00:00:00. I cannot figure out how to get the text to show at date only, formatted as x/xx/xxxx.

Re: Auto-populate Pre-determined Text if field is blank

Hilary Stoupa — Thu, 29 Dec 2016 13:17:44 GMT

Rules fire when a value changes. However, when you query a data source, on the initial query, rules on those fields DON'T fire. Put the rule on wherever the user enters the building number (you call it the "building number entry process" above). I'm assuming that is when you query the secondary data source?

Re: Auto-populate Pre-determined Text if field is blank

Curtis — Tue, 27 Dec 2016 17:47:48 GMT

Can you clarify where the following should be accomplished..."So put a rule on the ID field to set the date (or whatever) field based on a condition...."? Should the rule be attached to the ID field in the data connection, and should that be an action that set field value of the Date of Last Inspection -if blank- to whatever text I want instead a blank field?

Re: Auto-populate Pre-determined Text if field is blank

Hilary Stoupa — Tue, 27 Dec 2016 13:22:49 GMT

The problem with the secondary data is when you query it, each row needs to be evaluated. The rule on the building number field would only work to set every single row to the same value - not what you want. Instead, "repurpose" another field in the secondary data connection. You are probably not using the ID field, right? So put a rule on the ID field to set the date (or whatever) field based on a condition.... then, on the building number, set ID (after querying the data source) to something it will never normally be, like 0. OR, just use calculated values and conditions to display either the value or the text.

Re: Auto-populate Pre-determined Text if field is blank

Curtis — Tue, 27 Dec 2016 03:42:31 GMT

I'm an InfoPath simpleton. Yes, I'll need you to provide more detail, please. Let's assume I'm a five year old; now, tell me how and where, again.

Re: Auto-populate Pre-determined Text if field is blank

Patrick Halstead — Tue, 27 Dec 2016 02:03:03 GMT

Hi Curtis,

To get this technique to work in a repeating table requires an xpath that remembers the current() row that you are on (assuming you want to have a condition for the previous column).

Let's say you have three columns and you want to condition the value for column two on column one. You would use the same kind of rule for the repeating field, but just prefix the field1 (column one) with current()/my:field1.

Please let me know if that is enough to figure it out. If not, I can provide more details.

Cheers!

Patrick

Re: Auto-populate Pre-determined Text if field is blank

Curtis — Mon, 26 Dec 2016 19:12:40 GMT

Worked like a charm. Now, can I do the same for a similar field in a repeating table? The table data is coming from a secondary connection, activated by the same building number entry process as the previous post.

Re: Auto-populate Pre-determined Text if field is blank

Hilary Stoupa — Mon, 26 Dec 2016 01:35:25 GMT

Sure. In the same rules you are using when the building number is entered, have the last rule check to see if the Date of Previous Inspection field is blank, and if it is, set the field's value. I wouldn't set a date field to text, myself - but if your date isn't actually a date, it should be fine. Or use another field.

Re: Auto-populate Pre-determined Text if field is blank

Curtis — Sat, 24 Dec 2016 16:40:58 GMT

This may sound like the same issue, but it's not. Can an InfoPath Date field be self-aware? I have a form that has a field called Date of Previous Inspection. The data for that field comes from a SP list column of the same name. The field, as well as several others, auto-populates (and it does work right now), whenever the user enters the building number in the form. The problem is not every building has been previously inspected; or, if it was a report has yet to be submitted. What I'm hoping, if after the building number has been entered and the form auto-populates there is no data in the Date of Previous Inspection field, a rule can fire that validates the field is empty and fill the field with 'No History of Previous Inspection'?

Re: Auto-populate Pre-determined Text if field is blank

Curtis — Fri, 23 Dec 2016 17:28:45 GMT

Tada! Like magic. As always, you're the best. Do you know anything about SP email workflows?

Re: Auto-populate Pre-determined Text if field is blank

Hilary Stoupa — Fri, 23 Dec 2016 17:03:44 GMT

You don't want to Insert Function for the value, your text just goes in the value box. If you are in "insert function" you can put the text in quotes - that will make it a valid "formula".