Jump to content
tonyMONK

Copying Field Information

Recommended Posts

tonyMONK

I have a client record with, among other things, a field for a business name and another for the name of a non-business client (data would only appear in one of these fields).

 

I have created a field to place on a layout, in which I want to appear either one name or the other í.e. if there is a business name then that would appear (result1) otherwise the non-business name would appear (result2). The logical choice for an operator is the if function. Using this I can get result1 but not result2. Actually, I can get result2 in the case of a new record in which nothing has been entered in business name. However, if any data in business name has been taken out, and a non-business name entered, then no data appears in my field. It's almost as if there still appears to be data in the business field and therefore this appears as a blank as a result.

 

Thanks for any help.

Share this post


Link to post
Share on other sites
AHunter3

You didn't post the formula you're trying to use. You can use "If" (I tend to use "Case" but "If" works...makes no difference if you've only got 2 alternatives).

 

Are you using If (not IsEmpty (field), field, otherfield)) ,? Or simply If (field, field, otherfield)? Or some other formulation?

 

Either of these two formulations should work.

Share this post


Link to post
Share on other sites
tonyMONK

I've tried using if( isempty(field 1) ; field2; field1), which I think is the same as your not isempty suggestion, and also if(field1 not = "" ; field2 ; field1), but both yield the same result.

Share this post


Link to post
Share on other sites
comment

If ( IsEmpty ( field1 ) ; field2 ; field1 )

should work. If it doesn't produce field2 where you expect, then most likely field1 is NOT entirely empty. Check for spaces, carriage returns etc.

Share this post


Link to post
Share on other sites
tonyMONK

Yes, this seems to be the case as, even though field1 appears to be blank, when I put the cursor over it and delete the field info, field 2 magically appears. I have imported data into this table so I suppose that the spaces may have come about through that.

 

Is there no way to ensure that when there is no data in a field it is also effectively free of spaces etc??

Share this post


Link to post
Share on other sites
AHunter3

Well, you can do this:

 

If (IsEmpty (Substitute(TableName::Field A, " ", "")), TableName::Field B, TableName::Field A)

 

 

If you also want it to ignore hard returns, you can do this:

 

If (IsEmpty (Substitute(Substitute(TableName::Field A, " ", ""), "¶", "")), TableName::Field B, TableName::Field A)

Share this post


Link to post
Share on other sites
comment
Is there no way to ensure that when there is no data in a field it is also effectively free of spaces etc??

 

Spaces in a field ARE data. I would suggest you clean up your data instead of trying to find a workaround.

Share this post


Link to post
Share on other sites
LaRetta

You can run Replace Field Contents (or use it within calculation or script). The calculation for any use would be simple:

 

Trim ( theField )

 

If the field is empty except for spaces (no matter how many spaces), it will remove them. If this field has any other leading or trailing errant spaces, it will clean them up as well. Back up first. You can add this calc to auto-enter directly on those fields you wish to protect. I usually don't unless a certain field continually ends up with extra spaces (such as note fields). It isn't necessary on number, date or container fields at all.

Share this post


Link to post
Share on other sites
tonyMONK

Thanks for these suggestions - I'll give them a try and see how we go.

 

I have another question related to copying fields that you may be able to help me with.

 

I have a table in which I have, among other fields, a date and amount field.

 

I need to post this data to an accounting program so it has to be in a certain format. I've therefore created another date and another amount field as clones, which are set up in the required format ready for export.

 

In these clones fields I've used a calculation (by simply putting the name of the reference fields) to get the required values into the fields. This is fine except when the date or amount in the first fields is changed. In this case the data in the clone fields doesn't change and so is not correct. How can I make this happen??

 

Thanks

Share this post


Link to post
Share on other sites
LaRetta

Tony, two points:

 

1) You don't need to make the clone fields and 2) You don't need to make the clone fields.

 

When you export, be on a layout which DISPLAYS the field data as you wish (for the export) using field-level format. Then in Export dialog, check 'Apply current layouts data formatting to exported data.' That should fix you up. Occasionally some forms of export need calculations fields (such as fixed-length). But I wouldn't know what to suggest since you haven't told us the type of exported format you need for the accounting program.

 

LaRetta smiley-smile

Share this post


Link to post
Share on other sites
tonyMONK

Thanks for this help LaRetta. I didn't realise the possibility existed to apply a specific data format when exporting. I think that this will solve my main problem.

 

The export format unfortunately has to be fixed length. Also the fields required to be in specific locations on each line and there will be gaps between the fields which is for data that in my case is not necessary. i.e. first 6 characters a serial number, the following 8 characters a date, next 6 charcters blank, next 6 the account number, next 35 descirption, next 40 blank, next 8 debit amount, next 8 credit amount etc. How do I achieve this?

 

Thanks again

Share this post


Link to post
Share on other sites
AHunter3
The export format unfortunately has to be fixed length. Also the fields required to be in specific locations on each line and there will be gaps between the fields which is for data that in my case is not necessary. i.e. first 6 characters a serial number, the following 8 characters a date, next 6 charcters blank, next 6 the account number, next 35 descirption, next 40 blank, next 8 debit amount, next 8 credit amount etc. How do I achieve this?

 

This was what I was figuring you were dealing with. LaRetta is of course correct that if the format is a normal, nonweird dat format, one that you can apply via "Date Format" in layout mode to FileMaker's data fields, you don't need calc fields. Even if your necessary date format is 20060825, FileMaker will let you do that via custom Date Format options.

 

I was thinking of when I had to export to a plain-text file that would be read in by SAS on an IBM-VM running VMS. Space-delimited fields (i.e., field X starts at column 113 and ends at column 118 so there have to be blank spaces or spacer-characters taking up the extra room before the next field starts, or the field must be truncated if it's too long). And getting rid of characters that are FileMaker-legal but inappropriate for your destination, like hard returns in a FileMaker text field. For that kind of export, calc fields are generally your best bet.

Share this post


Link to post
Share on other sites
comment

The "correct" way to do a fixed-length export is thru XML, with a matching style sheet. An example style sheet is included in the Filemaker installation. However, XML/XSLT is very diffficult, and doing this inside Filemaker is very easy, so ...

 

All you need is a SINGLE calculation field (result is Text), along these lines (I am using "x" instead of space for readability):

 

Let (

pad = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

// PUT ENOUGH SPACES HERE TO COVER THE WORST CASE

;

Right ( pad & SerialID ; 6 ) &

Year ( Datefield ) & Right ( "00" & Month ( Datefield ) ; 2 ) & Right ( "00" & Day ( Datefield ) ; 2 ) &

Right ( pad ; 6 ) &

Right ( pad & AccountNumber ; 6 ) &

Right ( pad & Description ; 35 ) &

Right ( pad ; 40 )

// AND SO ON...

)

 

The date here is formatted as YYYYMMDD - switch the elements around as required.

Share this post


Link to post
Share on other sites
tonyMONK

Thanks, AHunter3. The date format that you cite is exactly the one that I have to use.

 

So on the export format I should change the field definition to calculation. But what formula do I use to achieve the fixed length, space delimited, fields and how do I place the data fields in the correct columns?

Share this post


Link to post
Share on other sites
AHunter3

Did you read comment's explanation?

 

You use a single calculated text field. That's what you export. You set up the formula thereof so as to result in the string of characters that the destination environment can cope with.

 

comment's example assumes that if you have 5 character's worth of data for what, in the destination environment, is hardwired to be a 10-character field, you put the blank spaces at the front of it, so for example if the column (i.e, field) is "City" and the value in a particular record is "Tampa", you want ".....Tampa" (where the dots represent blank spaces).

 

Examine comment's formula, I think it answers your question and gives you the recipe.

Share this post


Link to post
Share on other sites
tonyMONK

Sorry, I hadn't seen the points that Comment had made before my last post.

 

I've tried this and it looks like it might work, but can you tell me

 

- how can I get the spaces to appear after, and not before, the field contents. In Comment's example I assmed that this would be, for example

Right ( Description & pad ; 35)

but nothing appears when I use this, unlike

Right (SerialID & pad ; 6)

which comes out as expected

- I need to use carriage return because I need effectively 2 lines to appear, one for the debit entry in the accounting program, and one for the credit entry. The only real difference between the 2 lines is the placement of the debit and credit fields. When I look at the text file that has been created everthing appears on the one line but with the CR symbol showing where I placed it. Will the accounting program recognise the data following the CR as the 2nd line?

Share this post


Link to post
Share on other sites
AHunter3

To get the spaces to appear after instead of before, just invert comment's logic — instead of:

 

Right (Pad & Fieldname, AllocatedCharacterLength)

 

you do this:

 

Left (Fieldname & Pad, AllocatedCharacterLength)

 

 

Now you get "Tampa....." instead of ".....Tampa"

Share this post


Link to post
Share on other sites
comment
Will the accounting program recognise the data following the CR as the 2nd line?

 

Probably not. If you export as a tab-separated file, carriage returns in fields will be exported as vertical tab characters. Real carriage returns will be used only to separate real records.

 

Why don't you have debit and credit as separate records to begin with?

Share this post


Link to post
Share on other sites
tonyMONK

I've now tried the import but the accounting program does not recognise the CR and ignores and data after this. As I am importing this as a journal entry this doesn't balance and so the entry is invalid.

 

I need to have 2 lines for 2 reasons - 1) because the debit and credit amounts can't appear on the same line (because it is a journal entry) and 2) because the "account number" field, of which there is only one, is a different value for each line.

 

Also, does anyone know how I can get a serial number starting from 1 and incrementing by 1 each time the export is done, to place this in the calculation formula suggested by Comment? I have a serial number field in the table but, as I shall be doing the export every so often, the exported data will only start from the last number in the series. Is there a function for this?

Share this post


Link to post
Share on other sites
tonyMONK

The serial number is now not a problem as I find that I can use the field serial number that I have set up without any problem.

 

However, the CR is still a problem. Using the CR marker function in fm, on checking the resulting text file in Notepad prior to importation, a small box symbol appears where the return should send the rest of the data to the next line. If I do a manual return on this in the text file I can then import the data OK. But of course I want fm to do this for me. Any ideas how I can get the return function to work??

Share this post


Link to post
Share on other sites
comment

You haven't answered my question: why doesn't your solution keep debit and credit as separate records? I believe that would be your best option. Failing that, you could do one of the following:

- export as XML;

- use a script to put the entire dataset into a global field, and export that;

- find a way to automate the replacement of vertical tabs with carriage returns AFTER the export (i.e. not in Filemaker);

- use a plugin (I believe Troi File can do this?).

Share this post


Link to post
Share on other sites
tonyMONK

Thanks for the reply, Comment.

 

The debits and credits are already separate records.

 

I need to provide a bit more info about what I'm trying to do.

 

I have a table of bank transactions - 2 tables in fact. A bank account table and a bank line table. Th bank line table appears as a portal in a layout based on the bank account table. There are several bank accounts, so that each bank account record shows the corresponding transactions via the bank line portal. There may be a better way of doing this but this is what I have at present.

 

Therefore. each bank line entry is a record, and this is either a debit or credit, differentiated by a + or - sign.

 

The problem is that the text file be imported has to have 2 lines based on the one record - e.g. for an expense item, a debit to the expense and a credit to the bank. This is why I needed a carriage return in order to put the credit details for the bank (which has a constant value for the account number, unlike the first line where the account number will be different for each line) on the next line. I have been doing this for many years using DataEase for DOS without any problem. All that I had there was a procedure which mapped the 2 lines separately. This could either be printed or sent to a text file.

 

I suppose that I could have a separate record which just showed the bank details only but this seems somewhat tedious and I don't want this to appear on the portal as a separate line (but I could live with it if it would solve the problem!).

 

The other alternative is to create one journal entry for all the selected records i.e. debits for each expense and one total for the bank. However, I was rather hoping that I could get each transaction to appear separately.

 

Of the other suggestions that you have offered the Troi file plugin may be the simplest for me to implement.

 

As a matter of interest, further down the line I also want to be able to import invoice line details. This will be more problematical as there are 3 lines for each journal entry, debit to the client account (total = income + VAT), credit to income, credit to VAT. VAT = value added tax, in case you're not familiar with this term. In theory this would mean 2 carriage returns. However, there may be other ways around this which I'll investigate when the time comes.

Share this post


Link to post
Share on other sites
comment

OK, I see what you're up against. Sheesh, what kind of software actually expects two separate entries for a single transaction?

 

Here's another possible route: create a temp table just for the export. Find the transactions you need, and import them into the temp table TWICE, with each import having different mappings.

Share this post


Link to post
Share on other sites
tonyMONK

I have discovered that my accounting program can import data in .dbf format.

 

Will this make a difference in being able to resolve the carriage return problem?

 

What changes need to be made to the export calculation field formula provided by Comment in post #13 to produce a dbf file in the correct format?

 

Thanks

Share this post


Link to post
Share on other sites
AHunter3

You could perhaps dump the data as a plain text file, open it in a good text editor such as BBEdit or TextWrangler (I have no idea what Windows users use), and replace all vertical tab characters with genuine returns.

Share this post


Link to post
Share on other sites
comment

dbf or text - you are still facing the same problem of creating two records out of one. How does your accounting program monitor this? Does it reject an import that doesn't have double entries? Perhaps it could be as simple as exporting two files.

Share this post


Link to post
Share on other sites
tonyMONK

The accounting program only accepts entries in which the debit and credit totals are equal i.e. a journal entry. Unfortunately, this means that I can't just import one side of the entry first and then the other side later as separate records.

 

Presumably I could print the record in the right format - is there no way of transmitting the print to a text file?

 

Thanks

Share this post


Link to post
Share on other sites
comment

Not in Filemaker as such, but perhaps thru a Windows utility - I wouldn't know about that. I think so far I've mentioned five other options that can do what you need - three of those completely within FM.

Share this post


Link to post
Share on other sites
1Traks

Hi,

I'm doing the same thing as whats in this post. However, during the combining of fields, it is dropping any zero that is at the beginning or the end of the string. Any Ideas?

Share this post


Link to post
Share on other sites
Ender

Since there was quite a bit discussed in this thread, it would help if you specify exactly what calculation you've tried, what inputs you're using, and what you're hoping to get out.

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use