Scan field and insert thousands "," separator.

[vsiegle]

I need a script to scan through a text field that contains numbers and text and format the numbers with a thousands "," separator where needed.

The single text field contains a list of items separated by returns.

The number of items varies. May be as few as 10 or as many as 50.

The items will be either text or numbers (dollar amounts).

The first and last item will always have a "$" prefix followed by a space.

The items are similar to the following list;

$ 12456

300

incl.

1000

incl.

----------

$ 13756

I have accomplished this but only with a huge script that acts on the data as it is gathered, before it has been concatinated to the field. I would like to remove this kludge from my solution. Any ideas appreciated.

Thanks

[comment]

IMHO, the most convenient point for this is at the time of concatenation. Why? Because then you know which is which. Try doing it later, and you need to sift thru the pile and sort it out.

A simple formula for adding a currency symbol and thousand separator to a positive, integer, number:

Let (

L = Length ( InputField ) ;

"$" &

Case (

L > 6 ;

Left ( InputField ; L - 6) & "," & Middle ( InputField ; L - 5 ; 3 )

&

"," & Right ( InputField ; 3) ;

L > 3 ;

Left ( InputField ; L - 3 ) & "," & Right ( InputField ; 3 ) ;

InputField

)

)

For a more elaborate formula see here.

[vsiegle]

I like the case statment formula better than my if/then but I still need to resolve this problem. The formula below is what I use and it works but it must be applied to 200 individual fields on top of the calculations I have to preform on these fields as they are imported. Besides being hard to read when combined with the calcs I am at the max script length. It would be much more efficient to have it act on the concatenated list. I just have not been able to develope a scheme to get it done;

If( Length ( INPUTFIELD ) >= 4

and INPUTFIELD <> "incl.";

Left( INPUTFIELD ); Length( INPUTFIELD )-3)

& "," &

Right( INPUTFIELD )

& "

[comment]

In order to act on the concatenated field, you need to iterate individual lines. Doesn't seem very efficient to me.

I have no idea what you're trying to achieve overall, so these may be blanks:

1. You can place the formula in an auto-enter calculation, and perform it at the time of import;

2. A script can be broken into sub-scripts;

3. A formula that is hard to read is poorly written. Using Let() and comments, even a mile-long formula can be easily followed.

[vsiegle]

Each item in the concatenated field has been arrived at with a formula. Consider that a typical formula would consist of a half dozen case statements. Now cram these formulas into the parsing formula, multiply that by 200 items and well you can see that it is not pretty. I am trying a scheme that uses Let() to hold each item and then parse those variables. Another option is to just create a global field for each item and as you have suggested create an auto-enter calc.

I appreciate your feedback. Thanks

[comment]

Ehmm.. I don't think the auto-enter calc should be global.

Perhaps if you explained what is the input to the process, and what is the desired outcome, we could find a more convenient way to accomplish your purpose.

[vsiegle]

The solution is a construction quoting system. This part of the system allows a salesman to produce a quote based on the specifications entered on a worksheet. This creates an editable quote linked to the customer file.

The script in question gathers the input from a worksheet layout in the worksheet table, makes the necessary calculations to price each item and post the concatenated data to the cost field in the quotes layout in the quotes table.

A sister script gathers the input from the worksheet layout, makes the necessary calculations, writes a line item description for each price and post the concatenated data to the description field in the quotes layout in the quotes table.

The description and cost fields are single text fields, side by side on the quotes layout, expanded to 3 pages long, set not to print. They have identical merge fields sitting on top, mirroring the input. This allows the fields to be edited and to expand and print properly.

The script in place now works. What I am trying to accomplish is to reduce or eliminate the repetition of the "thousands" formula for each item gathered in the script.

[comment]

I am still experiencing a gap here. Because nothing in your description suggests the need for 200 fields, nor for elaborate scripts to describe/price each item. Unless all your line items are in a single record, and these script are designed to produce a report.

If that is the case, I suggest you break out the items to separate records in a LineItems table, and take advantage of Filemaker's built-in reporting abilities.

[vsiegle]

The problem is not the number of fields. I don't need 200 fields, there are 200 fields because that is how many fields it takes to gather the information needed to create the quote. The problem is that the data is both text and numeric and the only way in FM to parse it is one item at a time. FM's report module would not solve the problem. The data would still have to be parsed. I have coded this database in several programs over the years incl; HyperCard, Helix, 4D, FoxBase, Panorama and probably a few others. All these data bases allowed me to write something like a "For Next" loop and act on the chunk of data (the concatenated list). That's all I need from FileMaker. This is one of the few problems that I have had with FM that I have not found a good solution for. I have yet to work with the custom functions. Maybe a solution exist there.