Need function to string numbers like text

[Archit3kt]

Hi all,

I'm new to the forum--and new to programming FMP for my boss! I understand the logical concepts of programming, and how the FMP UI works for table relationships and scripts, but I don't know specific function commands. Now I'm trying to create a database for the manufacturing company I work for, and can't find a function to create a text string I want in a calculated field. Apologies if this question has already been answered, but I can't figure out how to word a search for this!!

I have two tables related exactly like the "Contact Management" and "Notes" tables in the starter template: each record in table 1 has an "k_ID" number that matches a group of records in the second table (all having that same number in their "kf_ID" field"). There is another field in table 2 whose contents are a digit from 0-9. This field is the "Item Batch #".

I want a calculated field ("Batch Code") in table 1 to paste the values of "table 2::Item Batch #" from ALL related records strung on end, whether there is 1 related record or 5. I've tried typing an example of what I mean....my company won't allow me to attach copies of our file, sorry. records are separated with ------------------------ lines.

EXAMPLE:

===========

==TABLE 1==

===========

k_ID: 01

Batch Code: 253

------------------------

k_ID: 02

Batch Code: 67

------------------------

===========

==TABLE 2==

===========

kf_ID: 01

Item Batch #: 2

------------------------

kf_ID: 01

Item Batch #: 5

------------------------

kf_ID: 01

Item Batch #: 3

------------------------

kf_ID: 02

Item Batch #: 6

------------------------

kf_ID: 02

Item Batch #: 7

I really appreciate the help, if I can have a solution by tomorrow morning it will mean MAJOR points with my boss! I'm on the west coast, so I'm rooting for all you people further east who'll be perusing the forum with your Monday morning coffee earlier than I will! Thanks again.

[Archit3kt]

If it were stringing values together from a finite list of different fields in one record, I would know to make the calculation Table2::field1 & Table2::field2 & Table2::field3 etc....but I don't know how to make it string the values from the SAME field from MULTIPLE records in a list, and only for all related records....rrrg.

[comment]

Try =

Substitute ( List ( Table2::ItemBatch ) ; ¶ ; "" )

---

P.S. Please use less abstract examples.

[Archit3kt]

SCORE! that does exactly what I want it to. Thank you so much.

And I apologize for the example; I agree, it would be easier to understand the question if I made a couple of sheets in excel and attached them, and used something more structurally apparent than just numbers. But wow, you immediately understood it and whipped out a simple formula that worked! Respect.

Now that I have this little nugget, I want to dissect it and figure out why it works....I looked up the Substitute function in the FMP help glossary, and it doesn't explain the parameters you entered. Could you explain what your formula tells FMP to do? their example is Substitute ( text ; searchstring ; replacestring). What does "list" do, and why "¶" and ""?

[Vaughan]

I looked up the Substitute function in the FMP help glossary, and it doesn't explain the parameters you entered.

Start in the middle and work outwards: read up on List(), then read up on Substitute().

[FMPierce]

•Table2::ItemBatch -- Obvious, it's a field

•List ( ) -- I think it gets the index of the field inside the parentheses (I'm guessing because I can't find it in the function reference in FM 11 advanced).

• Substitute ( ; ; ) -- Takes your field and takes any return and replaces it with nothing (in effect it takes out all returns).

Your Calculation:

Substitute ( List ( Table2::ItemBatch ) ; ¶ ; "" )

[comment]

I can't find it in the function reference

Well, really...

[Fitch]

List

[FMPierce]

Oh. Thanks. I must have missed it