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?

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

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, 200619 yr by Guest
Added comments

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, 200619 yr by Guest

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

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

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, 200619 yr by Guest

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

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

clones attached

ALL_Records_sans_Subcribers_Clone.zip

"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, 200619 yr by Guest
Added green

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?

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?

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

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

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.

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.

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)

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

deleting, removied lookup keys in issues sent, repoporting

....now

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

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.

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

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.

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

Before you ask those 11 do have a cuniquename

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, 200619 yr by Guest
Added comment

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

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, 200619 yr by Guest
Added Update

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?

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.

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

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

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

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

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

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?

In define database and relationships - your relationship graph, double-click the = between them and it will open. Re-select CompanyID on both sides and click CHANGE. You're almost there. :wink2:

all that is there is cuniquen name = cunique name, i shoud change toa to companyid both sides?

Correct. We're crossing a creek from using data which may change (to hold the relationship together) to a uniqueID which will NEVER change.

ok did that, and created the sent id, im going to change the issues sent out list to reference as many of records as i can then do the portal. i shoud make the protal in the records list or issues sent list?

There are many issues for one Company. This is a one-to-many (or 1:n) and you view portals from the ONE side (Companies). So on a Companies layout, create a portal of your Issues Sent.

One more thing. Both uniqueIDs (CompanyID and SentID) should be auto-enter but ALSO 'prohibit modification' should be checked. And never put these IDs on a layout (or in portal) where User can change them. If you display them anywhere, be sure to select the field, right-click and change behavior to NOT allow entry in Browse. If you change the CompanyID in your portal, ie, change the CompanyID in your Issues Sent, then you will break the relationship and that record will disappear from the portal.

Have fun. :smile2:

Edited August 11, 200619 yr by Guest
Added sentence

... but ... "im going to change the issues sent out list to reference as many of records as i can"

... they should already be related now. When you set the Issues Sent CompanyID with the Companies CompanyID. They should all be related (except for the wonky 11).

i was refering to this "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."

Right ON. ^)

Remember that you won't want to display Records::Last Name and so forth in the portal though. Users will already KNOW that information because it will appear on the main Records layout (each Company). But if you need to display from Issues, you can use Company fields directly. And you're rockin' now!

no the portal is just for Issue name, violune issue number, etc

Night LaRatta, tiem to leave the office ill work o nthe portal tomarro and give a report of how it goes

Have a good evening. :wink2:

well im back and im trying to add the portal, if i add it in issues sent list it does it per record and if i do it in record list it doesnt load anything. Am i missing something about portals?

correction: It only shows a hand full of records, out of 400+

on another note, since some of the records i had already manually combined milti line is their a quick way i can split them up? They all have the same format with a caridge return seperating them. or do i have to do it manually

"correction: It only shows a hand full of records, out of 400+ "

On each Records layout (Company), it will only show those records which relate, ie, have the same CompanyID.

How did you combine them into multiline? Did you create something like this?

IssueName (space) Issue (space) Sent On (space) ... and so forth, then carriage return then the same thing (another record) on the second line?

Addition: Because you will need to not only split them by record, but split them by FIELD. And if any field was blank ... well ...

Edited August 11, 200619 yr by Guest