Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Copying Field Information


tonyMONK
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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??

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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"

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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??

Link to comment
Share on other sites

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?).

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 1 year later...

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?

Link to comment
Share on other sites

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.

Link to comment
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.

 Share



×
×
  • Create New...

Important Information

Terms of Use