Hi,
I have updated/rebuild Quote form and in that added Shipto Address, but There are some lines which are blank because in customer master two address lines are blank.
Also I added Telephone Number in the form but the label which I added ad Contact Num: which I want to hide where data was not there in customer master.
Hi Sudesh,
Welcome! Can you show the report that you’re working on, what fields you want to go away etc? Are you trying to do this in SSRS?
Best suggestion is take a look how the invoice or the sales order uses those address details.
1 Like
Hi, I am preparing Quote form and want to add Ship to location. Please find below formula which I added but the data in address line 3 is blank so it appears blank in report.
Formula: [=Trim(First(Fields!ShipToName.Value))+vbcrlf+Trim(First(Fields!ShipToAddress1.Value))+vbcrlf+Trim(First(Fields!ShipToAddress2.Value))+space(2)+Trim(First(Fields!ShipToAddress3.Value))+vbcrlf+Trim(First(Fields!ShipToCity.Value))+space(2)+Trim(First(Fields!ShipToState.Value))+space(2)+Trim(First(Fields!ShipToZip.Value))+space(2)+Trim(First(Fields!ShipToCountry.Value))
If I’m reading correctly, you want to get rid of white space. In this case, an address line that doesn’t hold any value, correct?
There’s a couple different ways to do it.
One, is to put each line of the address into its own textbox:
Then you can set the Visibility expression on each box so if it doesn’t have a value, the textbox is hidden and everything below it shifts up.
Another route is to keep it all in one textbox (like you have it), but you have to use if statements to control the context and whether to add line breaks:
=Fields!BTName.Value & VBCRLF & Fields!BTAddress1.Value & VBCRLF & IIF(Fields!BTAddress2.Value="","", Fields!BTAddress2.Value & VBCRLF) & IIF(Fields!BTAddress3.Value="","", Fields!BTAddress3.Value & VBCRLF) & Fields!BTCity.Value & ", " & Fields!BTState.Value & " " & Fields!BTZip.Value & VBCRLF & Fields!BTCountry.Value & VBCRLF
So, the first IIF statement in the above is, if Address Line 2 is blank, then return blank. IIF it is not blank, show the value and add a Line Break to start the next line. Repeat this for Address Line 3… Line 4… etc.
If you include the Line Break in your IIF statements, you won’t move to the next line unless you have a value to to add to that next line.
And again, if I’m understanding correctly, you have something like this:
A Label, and a phone number field.
Here is the expression for the phone number field:
If there is no phone number you want to hide the label: Then on the label textbox, add a Visibility expression:
For visibility expressions, you’re providing an arguement where if TRUE, hide the textbox. So in this example, I think it would be:
=iif(Fields!BTConPhone.Value="",true,false)
Welcome Sudesh!
Both @dcamlin and @Hally are spot on here. Kinetic has a really nice built-in system to handle addresses of all formats. New users often make the mistake of entering the addresses in Kinetic in the way they want to see it printed. But Kinetic wants you to define the address pieces separately and then use the Address Format to put them together. This starts with the country:
By default, without a format, you get the fields in the order shown on the form, but you can move these around by filling in this form. In this case, City and State will print on the same line.
You can override an address format at lower levels like Customer, Ship-To, Supplier, etc.
The Report Definition will return a calculated field with the address as a delimited list (“~”). For example, in the Packing Slip, the first textbox is set to this:
And if I recall correctly, it removes the blank lines for you, but you can test for this.
Just had to do some work on addresses yesterday
I think the blank fields are removed when the data is collected and put into the temp db and stored in the calc_BillToAddress.
I am back on that job later so will update this post…
I have sort of wondered why they just didn’t have one field with the whole address with a seporator of VbCrLF between each split and have the filed set to can grow true and can shrrink true.
Another observation I have seen relating to white space, is it you add additional fields to the left of those address fields, depending on the location it will artificially move the address fields down when you run the report “in Report Builder” don’t be tricked Make sure you check the print view to confirm… Burnt myself on that (Again 
) wasting time.
2 Likes
yeah, that’s why you might see reports with stupid, empty textboxes in them like the red one mocked in below:
So if fields are “hidden” things can’t move left, or shrink smaller vertically because there is an object there.
messy.
1 Like
Here is an example from the invoice where the address information is just one text box
=split(Fields!Calc_ShipToAddressList.Value, "~")(0).ToString() &
code.AddNextLine(iif(Fields!Calc_MulShipTo.Value = false, split(Fields!Calc_ShipToAddressList.Value, "~")(1).ToString(), "")) &
code.AddNextLine(iif(Fields!Calc_MulShipTo.Value = false, split(Fields!Calc_ShipToAddressList.Value, "~")(2).ToString(), "")) &
code.AddNextLine(iif(Fields!Calc_MulShipTo.Value = false, split(Fields!Calc_ShipToAddressList.Value, "~")(3).ToString(), "")) &
code.AddNextLine(iif(Fields!Calc_MulShipTo.Value = false, split(Fields!Calc_ShipToAddressList.Value, "~")(4).ToString(), "")) &
code.AddNextLine(iif(Fields!Calc_MulShipTo.Value = false, split(Fields!Calc_ShipToAddressList.Value, "~")(5).ToString(), "")) &
code.AddNextLine(iif(Fields!Calc_MulShipTo.Value = false, split(Fields!Calc_ShipToAddressList.Value, "~")(6).ToString(), "")) &
code.AddNextLine(iif(Fields!Calc_MulShipTo.Value = false, split(Fields!Calc_ShipToAddressList.Value, "~")(7).ToString(), ""))
Only fields that are populated are shown.
Thank you Its worked
1 Like