Calculation field showing field from all records

[eddyb2]

Hi all

This could have come under relationships, or even exporting, wasn't sure where to post so tried here, hope that's right!

What I am trying to do is a 2 stage process...

1) I have a table, this table only contains 1 field (my calc field) and this table will only ever have 1 record.

The field is a text calc field

I would like it to look at another table (called base) and look at 1 particular field (called Name)

Then return the Name for all records in that table

So the text field would just look like

Fred

Mike

John

Phil

And so on

My next task is to then export this one field as a text document, containing the format

So the text document would look exactly like the calculated field...

Fred

Mike

John

Phil

Is this possible at all - I am stuck at the first hurdle, trying to get the calc field to show the 1 field for all records in the base table.

Many thanks

EddyB

[bcooney]

Can you confirm the version of FM you're using? Your profile says FM6. This is trivial in FM9, as you would use the List function.

In previous versions, you would create a value list based on the field contents. However, only unique values will list.

Why not just export a csv list of the Name field from your base table?

[eddyb2]

Hi

Thanks for your reply. Sorry I thoguth I had changed the versions

I am using Client 9 Advanced and Server 9 Advanced running on Windows XP / Server 2008

I will have a look at the list function and see what I can do with that.

I cannot export the Name Field as a CSV fromt he base table as I havent actually given all the details.

The calculated field will include some hardcoded text. What I am actually doing is building up a request file that gets posted to an FTP site for a different application.

So I have to put my request codes in there as well..

It would end up being something like

{LOGON} XXXX002

{FORMAT}

DCDEFLT=Y

MERGE=Y

TRAN=N

ROW=Y

MNEMONIC=Y

{EXTRACTION} 01

{TRIGGER}

NOW

{FORMAT}

SRVRFILEPREFIX=MyFile_%D%M%Y

{REQTYPE} S -1D

{DATATYPES}

NAME

P

NOSH

MV

LYE

ISOCUR

{NAMES}

Frank

Paul

John

Phil

George

Hopefully you can see the type of file I am tryign to build - under {NAMES} that is where I need the name field for each record in the databaseThe only way I could think of to create this file from the dtabase was with the new table, and calculation field, hardcoding the other text into the calc then pulling in the names somehow from the base table.

I would then need to export the whole field as a text doc to have my request file.

Is this the easiest and best way to do it?

Hope that makes sense!

Many thanks

[bcooney]

Using List() in the calc should do it.

[eddyb2]

That works great! Many thanks

The only issue is that the exported file shows "" around the whole field - the program this is going into won't like it and will fail.

So it starts...

"{LOGON} XXX002

When it should be

{LOGON} XSCG002

Anyway to stop it from doing this?

Thanks

[eddyb2]

Sorry that should have been...

So it starts...

"{LOGON} XXX002

When it should be

{LOGON} XXX002

It is the " that is causing the issue, nothing to do with the login code!

[IdealData]

Try using the TAB separated export option.

[eddyb2]

Works perfect thank you both so much for your help!