Jump to content

extracting numbers into a workable format

Recommended Posts


I am very much a novice when it comes to databases and filemaker so please keep that in mind if your generous enough to answer my question.


I have a series of numbers listed in this sequence

2.8 9.10 9.13 14.20

3.0 7.0 13.3 13.6

0.4 1.7 4.10 6.12

2.5 2.9 4.11 6.11



these are actually football(AFL) scores

so what I am trying to do is to seperate the numbers

for each row. I would like to break the number down into 4 groups

so for example


group 1) 2.8 = 2*6 + 8 for an output shown as 20

group 2) 9.10 = 9*6 + 10 for an output shown as 64

group 3) 9.13 = 9*6 + 13 for an output shown as 67

group 4) 14.20 = 14*6 + 20 for an output shown as 104


so the first number before the decimal point needs to be multiplied by 6 and the second number just added to that number so 2*6 + 8(as shown above)

and I would then do the same for each line of numbers.


I can copy and paste each row of numbers into the database or import them all quickly using excel, but the numbers I need appear in 1 row and I have to seperate them as shown above. Is there any way of doing this without typing each number in seperately?





Share this post

Link to post
Share on other sites

You did not specifically say whether you have one ugly text field that has info in it like so:


2.8 9.10 9.13 14.20

3.0 7.0 13.3 13.6

0.4 1.7 4.10 6.12

2.5 2.9 4.11 6.11


... or several records each of which has data in it that looks like this:


2.8 9.10 9.13 14.20



If it's all in one field, do this:


define global number field g.RowNum

define global text field, g.BigTextField

define global text field, g.Row


then for the top part of your script, the outer loop of it, you do this:


Set Field [YourTable::g.RowNum; 1]

Set Field [YourTable::g.BigTextfield; "¶" & YourTable::Original BigUglyTextField & "¶"]


..Exit Loop If [YourTable::g.RowNum+ 1 > PatternCount (g.BigTextField, "¶")]

..Set Field [YourTable::g.Row; Middle (YourTable::g.BigTextField; Position (YourTable::g.BigTextField "¶"; 1; YourTable::g.RowNum)+1; Position (YourTable::g.BigTextField; "¶"; 1; YourTable::g.RowNum+1) - Position (YourTable::g.BigTextField ; "¶"; 1; $YourTable::RowNum) - 1]


{that's your Row}







Otherwise, if each record contains just a single row, you just loop through your records, go to record [Next, Exit After last] end loop, and each Row is the field in that record containing the row of information. In this example I will assume each row is a record so I will refer to the row as Row, a field containing one row; if it was all in one BigTextField, you would use your global field g.Row instead.


Either way, for each row you do an inner loop that parses your data like so:




you'll need more global fields for the inner loop:


define global number field Pos

define global number field End

define global text field ScoreStr

define global number field Big

define global number field Little

define global number field Score

define global number field KeyField





Set Variable [YourTable::Pos; 1]


..Exit Loop If [YourTable::Pos > 4]

..Set Field [YourTable::End; Case (YourTable::Pos=4; Length (Row); Position (Row; "." 1; YourTable::Pos) ) ]

..Set Field [YourTable::ScoreStr; Middle (Row; Position (Row; "."; 1; YourTable::Pos)-1; YourTable::End - Position (Row; "."; 1; YourTable::Pos)+1]

..Set Field [YourTable::Big; Left (YourTable::ScoreStr; Position (YourTable::ScoreStr; ".") -1)]

..Set Field [YourTable::Little; Middle (YourTable::ScoreStr; Position (YourTable::ScoreStr; "."; 1; 1)+1; Length (YourTable::ScoreStr) ) ]

..Set Field [YourTable::Score; (YourTable::Big * 6) + YourTable::Little]


..Comment [That's your fully extracted score, for whichever YourTable::Pos you are on, out of four that were in the Row]

..Comment [You didn't say where you were recording these. I will assume you have a table of Scores and want the four scores related to the current record containing the Row; if not, adjust this part accordingly:]


..Set Field [YourTable::KeyField; YourLocalTableWithRows::Serial Number]

..New Window ["WriteOut"]

..Go to Layout ["Scores" (Scores)]


..New Record

..Set Field [scores::Foreign Key; YourTable::KeyField]

..Set Field [scores::Score; YourTable::Score]

..Set Field [score::Which Score; YourTable::Pos]

..Close Window ["WriteOut"]


..Comment [done with writing out the score, onward to next YourTable::Pos value]



..Set Variable [YourTable::Pos; YourTable::Pos + 1]

End Loop



This post was edited to remove references to GetValue and to Set Variable, which did not exist in FileMaker 7

Share this post

Link to post
Share on other sites

thanks for the great response, I will see how I get on with you answer/s





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.

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