Un-multilining multiple fields.

[LLX]

in my file i started concatoniating fields that shared company info with multi line fields.

no field should be empty (unless i hadent finished entering the data manually in wich case its moot the true copy record hasnt been deleted) any fields that were empty weir either place holder records or incomplete copies.

Any time a new records was multi lined they has issue name, volume, number, sent number, office from, and sent date while thing like company name and address remained a single line.

it was just a caridge return concatonation to combine the issue name, volume, etc. fields.

Now i want to undo the caridge return fields and split them into seperate records. any ideas?

[Ender]

It's not clear why this combining of different attributes was needed. You should always keep the original data in their own separate fields.

In any case, you can use the MiddleValues() function to pull the lines apart again:

Issue = MiddleValues(CombinedField; 1; 1)

Volume = MiddleValues(CombinedField; 2; 1)

etc.

[LLX]

its a long story. How does Middle Value work as apoint of reference?

[Ender]

I don't know what you mean "as apoint of reference." You can look it up in the application help if you want to know how it works.

[LLX]

i looked around in fmp and am unsure how i go about setting up middle value to split records

[LaRetta]

Here's a sample file which shows how to split them properly. Notice the calculation which will determine which fields contain two entries. I did not add ALL the fields in this calculation but I suggest you do - just to be sure you don't miss any.

And, because some fields may have two; some fields may have one; and some fields may have NO value, I'm using a generic Set field[] which can be easily duplicated so the same formula can be used. If any field contains one entry, it will assume the same for both records. In this file, I only included three fields - Issue Name which has two entries, OfficeFrom which is blank on some and contains one entry on some, and CompanyName which only has one entry. The first set of Set Fields[] grabs the second entry (or the first if it is the only entry). The second group of Set fields[] always grabs the first entry (or no entries if blank).

So add all your fields to the calculation and add all your fields to the script (inside the loop). Just duplicate BOTH Set Fields[] above and below the Omit. And back up before running this through your real data. It isn't very pretty ... but then, neither is your data. But you now know never to combine like this so hopefully this will be the last time you'll have to run something similar. Oh ... you mentioned numbers and dates. I hope these are actually text fields here. I assume so because FM wouldn't let you put dates or numbers as a multiline. But after correcting this, change them to proper dates and numbers as well. Good luck to you, sir.

UPDATE: Notice for the test I only set a few fields. Volume didn't split because I wanted you to see which were originally double records. So include Volume within your calc and within the script. I think the file explains it pretty clearly but if not, just ask. I wanted to keep it very simple so you could see the idea.

LaRetta

Unmultiline.zip

Edited August 18, 2006 by Guest
Added Update

[LLX]

when i get a moment ill look at your file and your notes. Until then, thanks in advance

[LLX]

i looked at the definitions-> optoson for csplit record and only see

ValueCount ( IssueName ) = 2 or

ValueCount ( Volume) = 2 or

ValueCount ( Number) = 2 or

ValueCount ( SentNumber) = 2 or

ValueCount ( OfficeFrom) = 2 or

ValueCount ( SentDate) = 2

wheres the setfield etc calulations?

[LaRetta]

The remainder is in the Split Into Two Records script. :wink2:

[LaRetta]

Maybe I was unclear. All fields which might have double entries should be added into the calculation field you mentioned. Any records which contain a double field will then produce a 1 in the calculation field.

The script performs a find on this calculation for 1 (after you've added all the field names to the calc) and then performs the split on the resulting record set. But be sure to add all the fields to the script as well (in both sections above and below the omit). :wink2:

[LLX]

ya i understand that. What i dont understand is how do i perform the split i seethe 1s and 0 and i understand its making note of what recors are multi line but then how do i actually split them

[LaRetta]

how do i perform the split i seethe 1s and 0 and i understand its making note of what recors are multi line but then how do i actually split them

This post slipped by me! How do you split them? You run the script - it'll do it all. It will 1) find all records with a 1 in that calculation (meaning they have double datalines) and then 2) loop through all the records and split them into two records as you've specified. ^)

[LLX]

i must be missing something simple cause that just my question. So i see the script and understand what its supose to do what i dont get is where middlevalue() comes in and how i run the script. Do i just modify the option and "accept" the changes to run it or is their soemting more involve to "run" the script

[LaRetta]

I have no MiddleValue() in this file. Let's walk through it again. Download the file I've attached. Add your field names (any you might have modified with two values) into the calculation. Then open the script and add the same fields in both portions of the Set Field[] (above and below the omit) in the GetValue() lines.

When you are finished modifying the calc and script, click PERFORM in ScriptMaker to run the script. The script will find any records which need to be split and split them.

[LLX]

this is what i cant find "Then open the script and add the same fields in both portions of the Set Field[] (above and below the omit) in the GetValue() lines."

the only calulatiosn i can find are in the define database and is "ValueCount ( IssueName ) = 2 or

ValueCount ( Volume) = 2 or

ValueCount ( Number) = 2 or

ValueCount ( SentNumber) = 2 or

ValueCount ( OfficeFrom) = 2 or

ValueCount ( SentDate) = 2"

im probably missing some button somwwhere to view the script but for the life of me that all i can find.

[LaRetta]

We'll get there.

Open ScriptMaker, select the script and EDIT. You will see several Set Field[] steps. Select one of the Set Field steps (above the omit) then DUPLICATE. On the copied step, click SPECIFY and change the field to your new field. Then OK. Then open it's calculation (Calculated Result ..) SPECIFY. Change both appearances of the field name to your new field name (within the calculation).

Repeat/duplicate a Set Field below the omit and also add your field there (same process as above).

[LLX]

oh its in scripts--> Scriptmaker how did i miss that lol

[LLX]

ya i get it now, nowi only need to find a way to merge it into my existing document and then make a script ( i have a geral idea based on this) to merge duplicate data (we dont need 2 company ids for the same company every tiemi do a datk update dump)

[LLX]

Well i copied the sript into my companies file but all it seemed to do (after proper adjsutment of couse) was blank/clear the line that had miltiple line data. Maybe i coded it wrong?

Companies-Split_Clone.zip

[LaRetta]

You have nothing in your Find Request. Add cSplitRecord and put 1 in the criteria. Also, you must remove the /* and */. FileMaker commented all your script lines out and they won't work that way. I assume you imported the script? FileMaker should have told you there were errors in the script. Anyway, if you make those two changes, it should work for you. Be sure to remove the /* and */ from ALL lines in the script. :wink2:

[LLX]

i have cSplitRecord, humm i'll remove the comments i thought they looked familiar.

see attached, see their is a cSplitRecord field and no comments now.

It doesnt blank/clkear the link that had a one before (liek it did last time) but i keep getting the find error

Companies-Split_Clone.zip

Edited September 7, 2006 by Guest

[LLX]

and now i see what you mean, find IN the Script part

Wel i added that and it seemed to work except the results while one record per line have a new problem. the data isn't being split its being duplicated.

say i have 2 records

issue name: June 2006, issue number 4

issue name: march/april 2006, issue number 2

etc

i end up with 2 records for march april and no mention of the june

Edited September 7, 2006 by Guest

[LaRetta]

Select ScriptMaker. Select your script and click EDIT. You will see several Set Field [] lines.

Do you see the calculations there? They will look like:

Set Field [ Unmultiline::IssueName ; GetValue (Unmultiline::IssueName ... ]

You will see 3 or four lines like this then an Omit script-step then several more Set Field [ ] lines.

Look to your lower right. Specify Target Field is where you select the field you want to apply the calculation to. Right below that is a button called Calculated result SPECIFY. So select the first Set Field[ ] line then click that button. That will open the calculation so you can change it. It is THIS calculation which changes the data in each field. After you change each calculation, say OK to exit the calculation dialog and move to the next Set Field [ ] line.

After you make sure all lines are correct (above and below the first omit), say OK to save the script.

UPDATE: If it isn't working, it is because you don't have that field both above and below the first omit. Look at my demo file and see what it splits and how. If that field isn't splitting, then that field is wrong in one of the two places - or it's NOT in a Set Field[] at all.

Edited September 7, 2006 by Guest
Added UPDATE

[LLX]

i found the problem my fields below omi wer a copy of the above line when they shoud have had a "1" at the end

[LLX]

ok i'm looking through my database and am noticing soem diplicate entries (soem are sililiar such as the same company but different reps and addresses so i want to ignore them) how shoud i go about combining my duplicate Records and reaigning the CompanyID referenced in Issues Sent List to 1 id?

[LaRetta]

I had not seen the file you attached right above. There is still no find request in it and I see you have the wrong calculations for the second set. It isn't a matter of just adding a 1. Use my file and duplicate the lines exactly.

Remember also that you didn't indicate how you split them, ie, if the second record didn't have a value in a field for the second record, did you put the first value then carriage return? If not, the system will assume it should be the same value in both records. Same is true if the first record had no value in a field but the second did ... did you put a carriage return THEN the second value? Only you can work this through. I doubt you will ever combine two records into one record (field by field) again. I wish I could help more but only you know what exactly you did on each record.

how shoud i go about combining my duplicate Records and reaigning the CompanyID referenced in Issues Sent List to 1 id?

I have no idea what you mean. If a field only has one value in one field but two values in other fields, it will duplicate that value (for that one field) for both records. Otherwise, how will it know whether the one value goes with the first record or the second? What did you do when you combined them? What was your theory?

Edited September 8, 2006 by Guest

[LLX]

on the first point ya the attackment was before i added the find request. When iwas referign to "adding a 1" i was refering to the "1" in the fields under omit as in your sample. I fixed that and it slpit them fine.

And yes i used a caridge return to seperate them. And no i wont be doing that again!

On the second thing.

Up until now weve been conceredwti the IssuesSent Table. Now im concered with the Records Table.

Some records are duplicates, a problem of geting issues sent updates as a txt file with existing and new sents mixed togeter is i just do a bulk add.

I need to figure out a script that would look for exact duplicate records. remove all but one. And update any Issues Sent that uses the duplicates Company ID and replace it with the id of the singular record.

Did that make sense?

[LLX]

And before i forget.

Thanks for the help, youve been a invaluable asset in getingthe hang of filemaker here.

[LaRetta]

You're quite welcome for the help. I would post a new question. And when you do, attach an example or explain it more thoroughly (because I didn't understand your request so others may not as well) and spell properly - I have no idea what several sentences even mean because of your misspellings. Remember we can't see your file or get into your brain ... we only know what you put as WORDS in a post. The better you explain your needs, the better the response. :wink2:

[LLX]

done, and done....i hope i explained it well

http://fmforums.com/forum/showtopic.php?fid/36/tid/180217/pid/221126/post/last/#LAST