Multi line Automation

[LLX]

I jsut got Filemaker for my client andi got a good handle on it. Experience with Excel, SQL, C++, and PHP was a good primer. Anyway my DB started as a a per sent mailing out list.

ie

record 1 sent 4 books to X company on Y, if volume = 1, and issue number = 2 name = Q

record 2 sent 4 books to X company on Y, if volume = 1, and issue number = 2 name = Q

record 3 sent 4 books to A company on G, if volume = 1, and issue number = 2 name = Q

etc

but my bosses want it revampled and now anything sent out to X is recorded in a multi row text field withthe volume, issue number, sent on, etc.

But i have over 500 records to resort this way it will take forever manually, is their a way to merge my fileds to get them to stack under each other?

[Lee Smith]

It sounds like you want a Portal using the Company ID as the Keys.

Either that, or try viewing the records using either the Table View or a New List View. Do a find for company X and sort buy Y...

HTH

Lee

[LaRetta]

I suggest you don't stack. Instead, use a relationship. Each company should be a record. So Company record X relates to a Sent table as:

Company table:

CompanyID: Auto-enter serial or uniqueID

Name: X

Sent table:

SentID: Auto-enter serial or uniqueID

CompanyID: X

NumbOfBooks: 4

DateSent:

Volume:

Issue:

Relationship would be: Company::CompanyID = Sent::CompanyID

And as Lee suggests, then use a portal to show what was sent to each company. If you turn them into multiline, you will constantly having to split the information again anyway.

LaRetta

Edited August 10, 2006 by Guest
Added comments

[LLX]

well i have a seperate DB with just the company info itself. This list takes that data and adds in the aditional sent data. Originall on a one for one basis but my bosses want it all combined. so all that is sent to X is listed under X's info

Company table:

CompanyID: Auto-enter serial or uniqueID

Name: X

Sent table:

SentID: Auto-enter serial or uniqueID

CompanyID: X

NumbOfBooks: 4

DateSent:

Volume:

Issue:

that basicly how i had it originally

i have the 2 tables, company list and sent list, with each sent beinga new record but they want to have all that is sent to X listed withthe reconrd for x ugh....

Edited August 10, 2006 by Guest

[LLX]

Note: I cloned the structure of of sent out list im goingto try to make a portal referecing it, wish me luck

[Lee Smith]

Create a relationship in the [color:blue]Company table to the [color:blue]Sent table using the [color:blue]Company ID:

Put a Portal on a Layout in Company Table, use the Relationship just established.

The portal can have any or all fields you wish to view, and will then be shown as a [color:blue]list in the portal when looking at the [color:blue]Company

HTH

Lee

[LLX]

i know when i enter entries to a tablei have it auto sort but i dont have a field set that stores that how would i go about grabing the automated id?

edit: Bugger!

So i copiled all my cliet lists(reviewed, subcribers, etc) into one master list to work myportal from.

My issue sent out list i created by using file inport to poplate it originally so i tried to for ma relationship with my Portal MAster client list by Type (contributor, reviewed etc), company name, last name, first name (filds that are indentical in both the master file and sent file) and went to make a sample portal.

i tell the portal to sort the "errors" field in the Set out Database and im getingback a blank result field. it says :Error in the edit box for the protal, and issue sent out is reading as a related field but its not loading in

Edited August 10, 2006 by Guest

[Lee Smith]

I had a reply made up, and then when I posted it you had changed the playing field.

Attached a clone of your file.

Lee

[LaRetta]

I suggest you also step back and list it on paper or whiteboard. You need to get some clarity here. It is fine for a business to tell you what they need (in fact, it is their obligation). But it is up to the Developer to determine how to pull it off behind the scenes.

If you break it apart to finite (every multiple to a table) then you will be able to adjust easily to any request they may have. Also, viewing the data and reporting will be piece of cake. Get clarity before you twist your structure around. Be sure the base is correct and the rest will take care of itself.

L

[LLX]

clones attached

ALL_Records_sans_Subcribers_Clone.zip

[LaRetta]

"I jsut got Filemaker for my client andi got a good handle on it. Experience with Excel, SQL, C++, and PHP"

This won't help you with relationships. I see no uniqueIDs in either file and they are CRITICAL. Was this a conversion from vs. 6? Unfortunately, this won't help us because you'll have to create unique IDs first and I can't see your other files to determine whether you've used IDs in any of the other files or what your overall structure looks like (to determine wheather you understand relationships). [color:green]But your attempt to join on all the fields indicates that you do not (at least yet).

Lee can walk you through it creating uniqueIDs and establishing base relationship between Contacts and Sent. In the meantime, my suggestion still stands ... get some clarity on paper - draw out your structure (connecting the dots via ID). :wink2:

Edited August 10, 2006 by Guest
Added green

[LLX]

when i orginally imported the data into my original DBs it asked if i wanted to auto create IDs, i checked yet. I just presumed it was there hidden somewhere.

In anycase cant change the past, can yonly prep the future. Since several fields in my DBs are the same how soudl i go about creatingthe ID and matching them across files?

[LaRetta]

Sure. Piece of cake.

How will you determine which Sent goes with which Company? Is the company name unique? You will need to find a unique identifier between them. What would those fields be? If you are sure the Company is unique then we can use that. If not, you will need to pull in something else to identify them. I suggest using the phone number field. What will you use to determine their uniqueness PER COMPANY?

[LLX]

well id use compnay if everyon was a company, sometime the company is blank but i have the last name to work with, when sorting id often go Company, Last name, First Name

[LaRetta]

I've imported your two files into one table. It contains no records. I've added the fields you will need to convert your files properly. This file will 1) Show you how to determine any duplicates and 2) Take you to the point of changing the relationship based upon a proper key.

So open to a layout based upon Records (this is your Company table). And import your Companies data. Be sure you are showing all records in your Companies file first. Upon import, you want to CHECK perform auto-enter. Then switch to a layout in this new file based upon Issues Sent. And import your Issues Sent file, again making sure all records are being shown in the Sent file and performing the auto-enter.

So when you have that done, post back and I or Lee will tell you how to reseralize and establish proper IDs into the CompanyID field in Records and then pull that ID into your Issues Sent table. You can then delete all the needless fields in Issues Sent as well (but wait until we get the proper IDs relating them).

Companies.zip

[LLX]

done, despite my tag im not a totally nubie

i noticed as they were importing the cunique name used the company name if thatwas unavaible it concatonated title through last name

next?

oh and thanks for helping me out.

[LaRetta]

Well now. I didn't realize you had lookups on those Issues Sent names. You will want to remove those first. You want to import only the raw data.

[LLX]

i never quite got the look ups to work, when i mad my issue sent list originallyi did a import record from the related table so their no look up in the tables (it was somethign i was planing to fix later after i figured out look up but thats moot here)

[LaRetta]

Hey, ignorance on one little subject is NOT stupidity. I am ignorant on thousands of subjects.

If it imported Title into cUniqueName then I suspect you mismapped a bit. Delete all records from both tables and do it again. And when the import opens, click the popup and select match 'by field name'.

L

[LLX]

deleting, removied lookup keys in issues sent, repoporting

....now

no title listed in the unique field, no look ups referenced in the design tables

[LaRetta]

You are on the ball. I like that in a person.

Switch to Issues Sent List and scroll to far right. Yellow field. Does it say OK on EVERY record? To tell quickly, sort it ... look at first record then scroll to bottom and look at last record.

[LLX]

i try to milti task when i can...

it says ok on EVERY records that has a Telephone number. Unfortunatly not EVERY record has a phone number. (soe of our client only gave a fax which goes in alt number, or cell phone, or a email, or just a address. Annyoying isnt it?) that why i alwasy sorted by compnay then name. their not one single instance where Compnay and name is blank (though we do have clients who only give a first name and no last name)

i guess that the nature of the beast when you have a few hundred records

[LaRetta]

Okay. Open Issues Sent definitions and change that cfilterPhone calc to:

If (

not IsEmpty ( Records::cUniqueName ) ; "OK" )

Now see if you get an OK on every Issues Sent record.

[LLX]

almost all it missed the first 11 of 501 total records.

Before you ask those 11 do have a cuniquename

[LaRetta]

Ah. This is why I'm fanatical about checking the data on issues such as this. There is something about those 11 records which does not match the fields in question. What do they share in common on those fields which doesn't match the same Company fields. You can also tell by comparing the cUniqueIDs in the two tables.

I would bet that - in one of the two tables, something was CHANGED in those fields - either a name spelling change etc...

This is precisely the reason meaningful data should NOT be used to glue relationships together.

Edited August 10, 2006 by Guest
Added comment

[LLX]

if i find a difference should i just match them to the record list?

[LaRetta]

Exactly. You need 100% match here. When you have it, you can go to next steps:

Open field definitions for Records (Companies) and go to CompanyID. Select Options > Auto-Enter and check ‘serial.’ Leave the rest as it is. Go to your Issues Sent and create a CompanyID (text). This will be standard field in which you will insert the CompanyID. Now go to your first Records layout and put your cursor in the CompanyID field. Then select Records > Replace Field Contents. Replace with serial numbers. Notice the check right below. This will reset your auto-enter serial to the last record’s serial (plus 1) after it is done. Then click the Replace button. Go to Records List and make sure it serialized all CompanyIDs.

Then go to Issues Sent list. Be sure all records are showing. Place your new Issues Sent CompanyID field on it and place cursor in it. Again select Records > Replace Field Contents but this time, we will pull the newly created CompanyID (THROUGH the relationship based upon cUniqueID). So open Replace Field Contents and Replace with Calculated Result. Select your Records table from the popup above and double-click the CompanyID field. Say OK and then REPLACE.

UPDATE: I was going to stop here until I knew everything went smoothly. Re-establishing keys is not for the faint of heart. If all worked well, open your graph and change the key fields to:

Records::CompanyID = Issues Sent::CompanyID

Then do your portal as Lee described.

Edited August 10, 2006 by Guest
Added Update

[LLX]

how shoud i deal with this, 4 enties in my sent list are not in my records DB. Their subcribers which i purposly omited sicne the way thei are recorded is vastly different andh ave a built in selt out list.

Shoudi just delete the 4 for my sent list?

[LaRetta]

Only you can decide that. But why are they 'vastly' different? Because you want all Companies in only one DB. It is up to you. If vastly different then those fields that are different can be in ANOTHER table. You would still have that Company and its CompanyID in the Companies table, then have the different fields related (again on CompanyID) in a one-to-one relationship to the other table.

[LLX]

Originally when i made the sent table i included subsubribers.

Later in subcribers table i added in issues sent out to ad clarity since subcribers are listed intheir table with details like term, issues remaining etc. i think i'll leave them in the db for now though

[LLX]

i did everything up to this point "Place your new Issues Sent CompanyID field on it and place cursor in it."

i have the company id in sent issues but how do i add it to the sent list

[LaRetta]

Okay, well if everything went as planned, you now have a good relationship. Be sure to also create an auto-enter serial in Issues Sent. IT has no related tables (yet) but that doesn't mean that you won't need it. So far, every time I leave off a uniqueID, I end up having to add one later. And if you ever need to import with update/add to, you'll need to an ID for the mapping.

To see the power of relational … go to your Issues Sent list layout and drop into Layout Mode. Double-click the Company field and change it to RECORDS Company field. Repeat with First Name and Last Name. When you go back to Browse, they should be filled in. The idea is … never add information more than once. Those fields in Issues Sent (Company, First Name, Last Name, Phone and so on) are unnecessary because they can be displayed through the relationship instead. I would first set a copy of this data aside. If someone changed any of the fields from within Issues Sent (such as a Company fax number), you will lose that information and you may need to find it again.

So the problem with redundant data is threefold: It wastes hard drive space, it slows your solution down and it causes mismatches, ie, if you change it in one place (Issues) it doesn’t change it correctly in Companies as well. But if you display the data through the relationship and you change it in the ONE table then it will display correctly everywhere. So you can now delete all those unnecessary duplicate fields. And of course the calcs we added (cUniqueName and cfilterPhone) can be removed from both as well.

Now ... to me ... we are watching magic happen in many ways. It is the birth of relational design and its power is why I'm in this business. If you still get stuck, don't sweat it. There are many wonderful people here on FM Forums who will help you through. But your portal should provide just what has been requested and you can decrease your file size. In fact, many of the benefits of relational won't even show until much later in your design but, believe me, it is well worth it.

L

[LaRetta]

...just read your question. How do you add it in the list? Drop into layout mode and select Insert > Field.

[LLX]

ah, though it was something tricky when it dint auto insert (like new fields normally do)

[LLX]

on this part "Records::CompanyID = Issues Sent::CompanyID" i do this in the set out list graph right not records, records lsit, or issue sent out?