It looks like the CustomerShipTo data source already has a state in field in it. I’m assuming it’s the abbreviation. Can you confirm that you see that and it shows up on your report? If you get that, you should be able to do a calculated field and just base it on the abbreviation to spell it out…
Yes that is the abbreviation. What would a calculated field for this look like to get the whole state spelled out?
1 Like
Then click the Field Source function button…
I would do a Switch statement and then list out each 2 digit abbreviation along with the spelled out name. I have copied and pasted the finished function to help you out…
=Switch(Fields!CustomerShipTo_State.Value = “AL”, “Alabama”,
Fields!CustomerShipTo_State.Value = “AK”, “Alaska”,
Fields!CustomerShipTo_State.Value = “AZ”, “Arizona”,
Fields!CustomerShipTo_State.Value = “AR”, “Arkansas”,
Fields!CustomerShipTo_State.Value = “CA”, “California”,
Fields!CustomerShipTo_State.Value = “CO”, “Colorado”,
Fields!CustomerShipTo_State.Value = “CT”, “Connecticut”,
Fields!CustomerShipTo_State.Value = “DE”, “Delaware”,
Fields!CustomerShipTo_State.Value = “FL”, “Florida”,
Fields!CustomerShipTo_State.Value = “GA”, “Georgia”,
Fields!CustomerShipTo_State.Value = “HI”, “Hawaii”,
Fields!CustomerShipTo_State.Value = “ID”, “Idaho”,
Fields!CustomerShipTo_State.Value = “IL”, “Illinois”,
Fields!CustomerShipTo_State.Value = “IN”, “Indiana”,
Fields!CustomerShipTo_State.Value = “IA”, “Iowa”,
Fields!CustomerShipTo_State.Value = “KS”, “Kansas”,
Fields!CustomerShipTo_State.Value = “KY”, “Kentucky”,
Fields!CustomerShipTo_State.Value = “LA”, “Louisiana”,
Fields!CustomerShipTo_State.Value = “ME”, “Maine”,
Fields!CustomerShipTo_State.Value = “MD”, “Maryland”,
Fields!CustomerShipTo_State.Value = “MA”, “Massachusetts”,
Fields!CustomerShipTo_State.Value = “MI”, “Michigan”,
Fields!CustomerShipTo_State.Value = “MN”, “Minnesota”,
Fields!CustomerShipTo_State.Value = “MS”, “Mississippi”,
Fields!CustomerShipTo_State.Value = “MO”, “Missouri”,
Fields!CustomerShipTo_State.Value = “MT”, “Montana”,
Fields!CustomerShipTo_State.Value = “NE”, “Nebraska”,
Fields!CustomerShipTo_State.Value = “NV”, “Nevada”,
Fields!CustomerShipTo_State.Value = “NH”, “New Hampshire”,
Fields!CustomerShipTo_State.Value = “NJ”, “New Jersey”,
Fields!CustomerShipTo_State.Value = “NM”, “New Mexico”,
Fields!CustomerShipTo_State.Value = “NY”, “New York”,
Fields!CustomerShipTo_State.Value = “NC”, “North Carolina”,
Fields!CustomerShipTo_State.Value = “ND”, “North Dakota”,
Fields!CustomerShipTo_State.Value = “OH”, “Ohio”,
Fields!CustomerShipTo_State.Value = “OK”, “Oklahoma”,
Fields!CustomerShipTo_State.Value = “OR”, “Oregon”,
Fields!CustomerShipTo_State.Value = “PA”, “Pennsylvania”,
Fields!CustomerShipTo_State.Value = “RI”, “Rhode Island”,
Fields!CustomerShipTo_State.Value = “SC”, “South Carolina”,
Fields!CustomerShipTo_State.Value = “SD”, “South Dakota”,
Fields!CustomerShipTo_State.Value = “TN”, “Tennessee”,
Fields!CustomerShipTo_State.Value = “TX”, “Texas”,
Fields!CustomerShipTo_State.Value = “UT”, “Utah”,
Fields!CustomerShipTo_State.Value = “VT”, “Vermont”,
Fields!CustomerShipTo_State.Value = “VA”, “Virginia”,
Fields!CustomerShipTo_State.Value = “WA”, “Washington”,
Fields!CustomerShipTo_State.Value = “WV”, “West Virginia”,
Fields!CustomerShipTo_State.Value = “WI”, “Wisconsin”,
Fields!CustomerShipTo_State.Value = “WY”, “Wyoming”)
In the future, if you ever need to construct something like this, I am a big fan of Excel. I was able to Google a list of states and their abbreviations and then paste that into Excel. Once it’s in Excel, you can insert the bits of code you need and do a concatenate on a row… then easily paste it to all the rows. Then that copies and pastes neatly back into a text editor.
As an interesting side note, the example they gave for how to use a switch statement is literally this exact use case.
2 Likes
I’m sorry… I wrote that thinking you were doing a report. This is for a dashboard? I would do the same thing except the syntax would be different for SQL. Did you make sense of it?
Yes this is for a dashboard, the syntax you provided gave an error. Do you have a tweak to use for a BAQ calc field? Also I am linking invchead to invcdtl to customer to shipto and then using the state field off the shipto. Would this be correct?
I would link straight from the Invoice to the Ship To because the Invoice will contain a reference to an alternate Ship To (if it’s not to the default specified on the Customer). Try this code. You may need to tweak the when statement depending on if you have any Alias or subqueries going on.
case
when ShipTo.State = 'AL' then 'Alabama'
when ShipTo.State = 'AK' then 'Alaska'
when ShipTo.State = 'AZ' then 'Arizona'
when ShipTo.State = 'AR' then 'Arkansas'
when ShipTo.State = 'CA' then 'California'
when ShipTo.State = 'CO' then 'Colorado'
when ShipTo.State = 'CT' then 'Connecticut'
when ShipTo.State = 'DE' then 'Delaware'
when ShipTo.State = 'FL' then 'Florida'
when ShipTo.State = 'GA' then 'Georgia'
when ShipTo.State = 'HI' then 'Hawaii'
when ShipTo.State = 'ID' then 'Idaho'
when ShipTo.State = 'IL' then 'Illinois'
when ShipTo.State = 'IN' then 'Indiana'
when ShipTo.State = 'IA' then 'Iowa'
when ShipTo.State = 'KS' then 'Kansas'
when ShipTo.State = 'KY' then 'Kentucky'
when ShipTo.State = 'LA' then 'Louisiana'
when ShipTo.State = 'ME' then 'Maine'
when ShipTo.State = 'MD' then 'Maryland'
when ShipTo.State = 'MA' then 'Massachusetts'
when ShipTo.State = 'MI' then 'Michigan'
when ShipTo.State = 'MN' then 'Minnesota'
when ShipTo.State = 'MS' then 'Mississippi'
when ShipTo.State = 'MO' then 'Missouri'
when ShipTo.State = 'MT' then 'Montana'
when ShipTo.State = 'NE' then 'Nebraska'
when ShipTo.State = 'NV' then 'Nevada'
when ShipTo.State = 'NH' then 'New Hampshire'
when ShipTo.State = 'NJ' then 'New Jersey'
when ShipTo.State = 'NM' then 'New Mexico'
when ShipTo.State = 'NY' then 'New York'
when ShipTo.State = 'NC' then 'North Carolina'
when ShipTo.State = 'ND' then 'North Dakota'
when ShipTo.State = 'OH' then 'Ohio'
when ShipTo.State = 'OK' then 'Oklahoma'
when ShipTo.State = 'OR' then 'Oregon'
when ShipTo.State = 'PA' then 'Pennsylvania'
when ShipTo.State = 'RI' then 'Rhode Island'
when ShipTo.State = 'SC' then 'South Carolina'
when ShipTo.State = 'SD' then 'South Dakota'
when ShipTo.State = 'TN' then 'Tennessee'
when ShipTo.State = 'TX' then 'Texas'
when ShipTo.State = 'UT' then 'Utah'
when ShipTo.State = 'VT' then 'Vermont'
when ShipTo.State = 'VA' then 'Virginia'
when ShipTo.State = 'WA' then 'Washington'
when ShipTo.State = 'WV' then 'West Virginia'
when ShipTo.State = 'WI' then 'Wisconsin'
when ShipTo.State = 'WY' then 'Wyoming'
else ''
end
1 Like
What field filter would need to link the shipto to the invoice? Also, link InvcHead or InvcDtl directly to ShipTo?
That seemed to work great!
The keys for ShipTo are Company, CustNum, ShipToNum… I’m not sure if InvcHead and/or InvcDtl have those fields in them. You could check both. I’m guessing InvcDtl would have it because it doesn’t seem unheard of to invoice multiple different ShipTos together as long as the BillTo was the same.
1 Like
For month order, you may try the function Month (expression) = 1 thru 12 as an interger.
The other area you should check out is using Advanced Grouping in the query. You can then total Various parameters all in the same query.
Bruce
If I wanted the cost value to be PartTran.ExtCost instead of the sum of all the individual costs, what would I need to change differently from the original way? How would the linkages between each phrase builder be established in order to get the details for all the specifics?
Instead of doing the case statement - I would consider setting up UserCodes for States.
In addition to linking the query to the UserCodes, you could use the table to validate the selected code or even put in an combo-box field linked to the usercode.
1 Like
I hadn’t considered this. But it would make sense so you only have to do it once.