calculated fields and exporting their data

[al kim]

hi, newbie here. first off, i tried searching but gave up after a coupla hours. apologies if this is a duplicate. i'm working on creating an export file by exporting only certain fields to match a flat file layout for another application. the flat file has specific length requirements, thus my fields in FM must be padded to fit those requirements. i'm using calculated field definitions to accommodate this need, but the logic i'm using for certain fields isn't quite right.

for example:

Case(export_responsecat="RSVPA ", "ATTENDED", export_responsecat="RSVPD ", "NO SHOW ", export_responsecat=" ", " ")

where if the export_responsecat field is RSVPA, then the export_responsetype field is ATTENDED, etc.

the reason for the space after NO SHOW is to make this fit the length requirement of 8 characters (same issue w/ the "null" case). in the near future, i'm gonna have to accommodate other field entries, which my code will not do efficiently. any ideas or suggestions?

i also have another quick question. i'm also exporting a date column into this flat file. the requirements for this field is MMDDYYYY. the calculated field code i'm using is just "TODAY," but this formats the date as MM/DD/YYYY (or however my machine is configged). the slashes add 2 extra spaces which throw my flat file off. i tried formatting this date field in FM, but it will not allow me since it is a calculated field. is there a work-around?

sorry for blabbing and thanks for looking!

[comment]

Try:

Left(

Substitute(Substitute(

export_responsecat,

"RSVPA", "ATTENDED") ,

"RSVPD", "NO SHOW" )

& "xxxxxxxx", 8 )

Replace the x's with spaces.

In your example, both "RSVPA " and "RSVPD " have spaces at the end - any special reason for that?

[al kim]

>In your example, both "RSVPA " and "RSVPD " have spaces at the end - any special reason for that?

// yes, that field in the flat file i'm trying to create must be 8 characters in length. the spaces are to pad that field to fulfill that requirement.

as for your suggestion, it won't work b/c these fields are contingent on what other fields values are:

field RSVP - if "will attend" then field export_responsecat = RSVPA, else if "declined" then = RSVPD

field export_responsecat - if "RSVPA" then field export_responsetype = ATTENDED, else if "RSVPD" then = NO SHOW

etc...

does that make sense? anyway, i'm trying to create a CASE calculation that will populate export_responsecat either as "ATTENDED," "NO SHOW," and whatever future responses i come up with, but that will also pad that response to make that field value a total of 8 characters.

the problem is that the export_responsecat values are variable in length (ie: ATTENDED vs. NO SHOW), so what kind of a statement can i code where it'll calculate the length of that value and then add the right amount of spaces to make a total of 8? my statement in the 1st post works but is static. for example, what if a future value for that field needs to be GUEST? again, i would have to use "GUESTxxx" (where x = space).

thanks though for taking the time to reply!

[comment]

does that make sense?

Not really - I am utterly confused regarding which is the input and which is the output.

From your first example it would seem that export_responsecat is the input (with values "RSVPA " or "RSVPD "), and export_responsetype is the output.

Now it seems that field RSVP is the real input, and both the other field are the output.

In any case, the principle suggested will work, once the I/O issue is resolved.

Let me put it in a general form:

outputField =

 Left(

Substitute(Substitute(

inputField,

"inputValue1", "outputValue1") ,

"inputValue2", "outputValue2" )

& "xxxxxxxx", 8 )  

 



Note that "outputValue1" and "outputValue2" have no trailing spaces; the required amount of spaces is auto-added by the surrounding Left() function.



You can do the same with a Case() function, if you like:



outputField = 

 Left(

Case(

inputField = "inputValue1", "outputValue1",

inputField = "inputValue2", "outputValue2")

& "xxxxxxxx", 8 )  

[al kim]

sorry for being inarticulate and thanks for being patient (yesterday was the first time using FM, so it's taking some time getting my head around concepts). i understand the logic of your snippet and it works for what i need. thanks again!