avoiding duplicates

[lingtheling]

I'm not very expert (newbie-ish)... and would appreciate any advice.

I've created two very simple databases to help me manage my student placements.

db - A contains student data including student placement details (company name / address etc)

db - B contains placement data (company name / address etc)

The problem I have right now is that I've designed it so that when completing the placement details from the student db it filters directly in to the placement db creating a new record. (I think I've done this through a relationship - but I'd be lying if I said I knew that was the correct terminology).

I'm happy with how this works. However, on occassion more than one student will work at the same company. Therefore, what I want to create is that on entry of a duplicate company name for the db B to not create a new record but to find the same company. Then to allow me to put a note on the existing company file of the students name (I've designed a wee script that drops the students name and date into a box - happy with this). The bit I'm struggling with is the duplication.

I've looked at information about relationships, and lookups - but I've not felt experienced enough to yet make use, or sense of what I've found. And after 4 hrs of blindly trying things tonight I hope that someone might be able to help.

FMP 11, mac os x

Many thanks

Lisa

[comment]

See if this helps:

http://fmforums.com/forum/topic/76428-creating-new-records-via-relationships/page__p__359746#entry359746

[lingtheling]

See if this helps:

http://fmforums.com/forum/topic/76428-creating-new-records-via-relationships/page__p__359746#entry359746

Hmmmmm- well, I looked at that last night, but I couldn't pick through and see how it related to my problem (though it clearly might - I can't see the connections).

I suppose the easiest way to describe my problem is via two separate problems (problem 1) that I want to be able to enter company contact information into db A, which then automatically filters into db B, but if there is a duplicate company not to make a new record, but instead to go to the original record and then (problem 2) to allow me to enter the students details into a separate field.

Has that helped any?

Thank you for your help.

[comment]

I understood your problem - but I am afraid it cannot work the way you describe:

• 
  
  
• In order to link a student to an existing company, you must enter the company information into a field in the Students table.
  
• In order to create a new company, you must enter data into a field of the Companies table (even though it is placed on the Students layout). And this will create a new company record - even if it's a duplicate.
  

I suggest you define a value list of existing companies and select from there. If it's a new company, have a script take you to a layout of Companies, create a new record and let you fill out the details.

The other important thing is that you should assign each Company a unique CompanyID and use it - and nothing else - to link between Companies and Students. In the Students table, there should be no fields that describe the company, other than the CompanyID of the selected company.

[lingtheling]

> but I am afraid it cannot work the way you describe:

Oh... really?

Linking the student to the company is a secondary issue really. I thought I sorta had a script to fix this - maybe. I feel I need to fix the company problem first.

You're right, the way I've designed it thus far - company information is completed in the students db first, it is linked to the placements db which creates a new company record (which is fine) but... 'even' if its a duplicate (Therein lies the problem).

I don't think a value list of existing companies is the way to go - there could be dozens of them.

And I'm not sure I understand why I'd create a unique company ID. That I'd get a new record should be enough - it's the duplication that is the bit I can't seem to fathom.

Would it help to see the files?

There must be a workaround?

Thanks for the help.

Lisa

[comment]

I don't think a value list of existing companies is the way to go - there could be dozens of them.

True, but if you begin typing, the drop-down/pop-up will jump to the matching value. Or you could use a portal or open up a new window to make the selection.

You could also enter the company name into a global field - then have a script find the name in the Companies table, or create a new record if it doesn't. However, this is problematic because if you mistype an existing name, a new record will be created.

I'm not sure I understand why I'd create a unique company ID

Because names change, get misspelled, etc. The idea is to enter the name once only (in the Companies record) and being able to modify it without breaking existing relationships.

[lingtheling]

Ok... ace!

can see some some light...

I didn't realise you could drop down from the fields, yea that makes some sense.

That might be enough.

I don't understand portals? Too much of a Newbie.

Sounds like potentially an elegant solution - how do I start with this idea.

[lingtheling]

Mmmm - actually, just had a look at the portal thing (briefly). Ok. I think I get it.

It actually allows you to see the fields from the other (placement) database within the one you are working on...

OK. I see that. Not sure why it's better though - though perhaps it is... ?

[comment]

Have a look at the attached - it shows both methods, a drop-down and a portal.

SelectParent.zip

[lingtheling]

Have a look at the attached - it shows both methods, a drop-down and a portal.

Thanks for this - all have really helped me to think through to how I might do it.

Light at the end of the tunnel perhaps.

I wonder if you can help me with one more related question...

(very new to portals)

I've created a portal - it gives me a grid / table (that I can define) then I choose which fields I want (all well and good). But it doesn't place the fields into the table. So now I"m looking at a blank table and a set of fields which I can use. Ummm - why the table, what does it do? Do I need it. Sorry if it sounds dumb - I can't see what's happening.

[comment]

I can't see what's happening.

Neither can I. Why don't you post your file?

[Vaughan]

I've created a portal - it gives me a grid / table (that I can define) then I choose which fields I want (all well and good). But it doesn't place the fields into the table. So now I"m looking at a blank table and a set of fields which I can use. Ummm - why the table, what does it do? Do I need it. Sorry if it sounds dumb - I can't see what's happening.

The word "table" has specific meaning in relational databases. Tables are defined with fields and data is populated in records. Generally each data "entity" requires a separate table.

A portal isn't a table: it is a visual interface used to display records from a related table. You need to specify which fields from the related table are shown in the portal, this can be done at portal creation using the wizard or any time afterwards manually by adding a field to the top row and selecting a field from the same table as the portal (or other related tables).

[lingtheling]

The word "table" has specific meaning in relational databases. Tables are defined with fields and data is populated in records. Generally each data "entity" requires a separate table.

A portal isn't a table: it is a visual interface used to display records from a related table. You need to specify which fields from the related table are shown in the portal, this can be done at portal creation using the wizard or any time afterwards manually by adding a field to the top row and selecting a field from the same table as the portal (or other related tables).

Sure - that all makes sense to me.

What I don't understand is - if the portal is a visual interface to display records from a related table, and once the fields have been chosen why don't they appear in the portal, but on the top row only - which means then you separate them out - are they still part of the portal or just related fields? I've attached a picture. On the right of the main db interface there is the smallish grey portal window with six cells, and then to the right of that contact details which I chose through the portal menu item. So I guess what I'm asking is what does the grey box do - how do I use it?

Ta

The file!

[Vaughan]

why don't they appear in the portal, but on the top row only

Errrm, the related fields have to fit within the top row of the portal. Check that the field objects are completely within the top row of the portal. If one pixel is out of the top row then FMP decides the object is not in the portal. (BTW this is a useful feature, not a bug.)

[lingtheling]

I don't see the logic (yet)... I've chosen 8 or thereabouts fields, which don't fit into the top row... so yes they then appeared outside of it. The portal window asked for no of rows etc and it defaulted to 6.

I've attached the two empty linked files.

Thank you for help

EMptydb\'s.zip

[comment]

I've chosen 8 or thereabouts fields, which don't fit into the top row

Then you need to make the portal wider and/or the fields narrower.

A few more notes:

1. Why 2 files, instead of one file with 2 tables?

2. How many placements can a student have? If it's only one, then you don't a portal to show placements (plural). If there can be more, you will need a third table for them.

3. What do you suppose will happen in your file when Bobby Shaftoe leaves Acme and Jack Zaybak takes his places?

4. Why are your files named "2011"? It seems like you intend to have a file for each year - there is no good reason for that, IMHO.

[Vaughan]

Get one of FMP's supplied templates (like Task Management) change into layout mode and look at how the portals there are set up. Note the size and position of the portal and the related fields.

Think of the top row of the portal as a box that the related field must fit into.

[lingtheling]

Ok...

I had two files and not one as it seemed the most logical way to do it (at the time).

Still I'm thinking that we will use the databases somewhat separately. The placement db to track students, and monitor their activities over a period of time. And yes, my thinking was to create an empty db each year for each new cohort. The placement db would be used more continuously since the placement providers could be contacted year in year out, and is more used as a contact db, with letters etc attached. I was thinking that a simple note in the placement db that showed which student(s) had worked there would be enough. Though looking at it now, I'm wondering if there is a portal system that might help... document this (now I'm sort of understanding a bit more about portals). Though am still puzzled on how to best work this - am pondering on it now.

I looked at putting two tables into the one db and then had problems building a relationship between them... ??

Got stuck and have for the minute shelved it... file attached if you can see what I'm not doing - please let me know.

I've looked at the portal in the 'Task Management' template file, and I can see how (if I'm looking at the right part) the portal has been used to create a list. However, this isn't what I was thinking since in the main students would only undertake one placement per yr (sometimes two).

Still fiddling.

Thanks for your help all

[lingtheling]

Hi... you now have me exploring the world of portals. I'm looking at the 'Task Management' system... which I think I might be able to make use of the way it potentially shows a list of things (placements) (would work for if a student does more than one... but now I'm looking at the Task Management system and have tried making a similar portal and its not working...

It's not making a grid and I can't see how to make it so, I can't see how it works in the Task Management one either... I'm clearly missing a bit of the jigsaw.

[comment]

You are not being entirely clear. I suggest you use a single file - it will make things easier for you, both in the short run as well as later on.

In order to see which students have been placed at a company, place a portal to the Students table on a layout of Companies. Of course, if you want to also see placements from previous years, you cannot have a separate file for each year.

You haven't answered my question #2 above.

---

BTW, I think you'd be better off creating your file from scratch. The templates can be tempting, but they also contain a lot of excess baggage you don't need and that can get confusing.

[lingtheling]

So...

I feel that I've made good progress...

I've decided to keep two db's one for students and one for placements. Still seems logical in my own mind to do this.

I've made a portal (I think I get it), which allows for us to enter basic placement details which feeds into the placement db, and pulls back in the information in dbA by using a lookup.

All well and good (feeling happy).

I've got a wee script which adds the student name to a field in the placement provides notes, that tells us who undertook the placement. I'm happy with this also - I think it's enough.

But I'm now back to where I started... in that as you point out each time a new record is entered.

So for example. I have a student who undertakes a placement at the AWF, it finds AWF from a drop down, pulls back their address details through the portal, but still makes a new record in the placements. So that's now the problem I can't see beyond.

And whilst I see the point of having a unique ID no for each placement, I still think this might be the same problem. ?

I feel like I'm almost there!

WedfmpTESTS.zip

[lingtheling]

Hi all...

I'm still stuck with this and still trying to play with it.... Any ideas?

Lisa

[lingtheling]

was that supposed to be helpful?

[lingtheling]

Well I don't.

Actually I tried - but I obviously did something wrong, and it didn't work so well.

I also wondered about the user end when it came to searching records and wondered if student records + placement records might produce a figure that could become distracting. So I gave up on that - and worked on a different logic.

Sorry for not living up to your exacting standards, but I wasn't born knowing how to reverse park either, it was something I had to learn, which is why I've come here to seek help. Through this little thread I feel that I've learnt a bundle of new tasks - so I thank all that have helped.

Your sarcasm hasn't.

And two other points:

• I will have another go at two tables in one file - but had decided to try it on a different project / experiment

• perhaps you can see how having two tables in one file might solve my problem. If this is the case, perhaps instead of mocking me - you might share?

Lisa

[Vaughan]

Bruce wasn't mocking you and his comment may have been direct but it wasn't sarcastic.

By his usual standard he was being polite and gentle. ;)

[lingtheling]

I might not be an 'expert' but I'm not stupid. I found that 'direct' reply not helpful - and not in the spirit of help forums.

You might as well tell me to go away and not ask stupid questions, which again is not in the spirit of help forums, and smacks of 'newbie' you're not welcome here.

My question isn't stupid, I've spent the last three nights trying to solve this problem and have found list after list of scripts that I've so far found somewhat impenetrable (my low level of experience is of course making reading through these more difficult) - so I've still not found an answer.

Any advice on my problem Vaughn or you just wanna stick up for your friend ;-)

[lingtheling]

Sure - I can see it's not as simple as I thought, but I do feel that I'm a cats whisker away from something that will work well enough.

Thank you for looking at it.

I've attached my table experiments (titled filename+experiments). The error that shows - is that the table is missing. I can see that this is to do with how the relationships are set up, though when I was doing it (and the point I gave up with it) - I'm sure I didn't understand (and tried some different options) why the relationships weren't working.

Also, as I explained before, and even looking at your file I fail to see why I would want them all in the same file in any case. I can see it makes it tidy, but in terms of interface, and for for the user, I felt it would make just as much sense to have them in two separate files. One for student and one for placement.

I've attached my latest stage, with dummy names and companies, and with all my mistakes and current scripting explorations. I don't need to separate out Parents, Companies etc (but that has illustrated for me how tables work in this way - thank you). I feel that I only need students records and placements (we need to detail contact details for these and dates), Projects are managed locally, but could be managed in the same way - and essentially presents the same problem.

Where I'm at with how I'd like it to work is for the placement to be attached to the student name. So I've created a portal that holds the placement address. This uses lookups to find the placement address. Currently what happens is that one puts in the company name and it creates a new address in the placement db (table), if it's an address that exists it still creates a new address and pulls the names back into the Student db.

What I would like to happen is when one enters an existing Company name into the student db, that if the Company exists then to go to the original record (not to create a duplicate and new record), but physically speaking to refer to the original entry, and then the user can press the put student name button, which allows us to see from the placement interface which student(s) have worked with that company.

I've been looking at this thread, but have had trouble adapting it.

http://forums.filemaker.com/posts/a7bdb69596

I hope that explains it a bit more clearly.

Thanks for looking at it Bruce.

StudPlacements 2.zip

[lingtheling]

1. I have seen that general advice from lots of avenues suggest a unique ID... but I haven't yet understood how this will help me - but have begun to look at this.

2. Yes, I understand the invalid field objects are back to the relationships - but as I pointed out before I'd actually abandoned this route since I couldn't see it working.

3. I haven't yet heard why I should have two tables in one database - and why this will solve my problem.

4. Sorry I don't understand what a 'fundamental data structure' is. Sorry for being so 'basic'.

Yes, actually - my design is a cats whisker away from being useful (in other words - I believe I can see I should be able to use it, even with its imperfections). Just because you say it isn't doesn't make it so.

Perhaps you'd like to be more specific in your advice rather than trying to point out my shortfalls as a filemaker database designer (which after all for me, is a tool that I'm trying to use and make work).

Why are you being so aggressive?

Lisa

[comment]

Lisa,

this thread is not going anywhere, because - pardon my being blunt - you are talking where you should be listening.

You HAVE been told why you should use IDs rather than names for relationships (post #6).

Having all your tables in a single file will NOT solve your problem. It will only make your solution easier to manage and less likely to break. Incidentally, it will also make it easier for us to explain what needs to be done. If these are not sufficient reasons, I would suggest that at this point you take this on faith.

I cannot help wondering what is your purpose in asking for professional advice, when you are not willing to follow it? This entire thread could be summarized as:

Q: I have a problem.

LOOP:

A: Do this.

Q: I don't want to do that. I have a problem.

END LOOP

After more than 30 posts, you are nowhere nearer a solution than when you started.

Please note also that you have been asked - twice - to clarify a point (post #17 and #21), but you haven't done so yet. You may have noticed that I have stopped responding at that point - because I cannot help someone who is sending out all signs of not willing to be helped.

[lingtheling]

Thank you for your reply.

Yes, I can see why an ID no is useful – what I haven’t been able to see is how to use them. And I’ve not seen any explanation of how to use them in this instance.

Fair enough that having all tables in a single file might help not to break. it I will try that again (might take me a few hrs), my current understanding still leaves me with the same problem – but ok, I will take it on faith and give it a bash.

However, see #17 below re my rationale from users end – hence my hesitation.

I have taken advice, and have learnt a great deal, many of my posts actually refer to this and give thanks (I am really grateful – honest!). But yes I’m still stuck, and tbh some of the advice floats without context which has left me feeling still stuck – but I have tried. Further, some of the advice has felt as if it’s gone away from my desired outcome (again, perhaps the lack of context makes this feel so).

Points #17

• Two files rather than one and two tables

a) I’ve not yet seen how making it one will fix my problem (but I will try it – promise!).

We will use the db’s for two separate tasks. One to organise students, Two to contact companies (#19).

c) My hesitation was also about how the users (admin staff) will view the interface and so as to try not to make it too confusing for them.

(but I will try again with 2 tables) (#27)

• How many placements can I student have

- They can have a few, it is typical for them to have one – sometimes three. I think the portal approach works for this, and I think (*hope*) that I’ve got this bit working.

• What will happen when Bobby and Jack leave

- nothing needs to happen really. Bobby’s and Jack’s name needs to be recorded on the company record in order that over time it is possible to see which students have undertaken opportunities. It acts really just as a note. (#31)

- each year there is a new cohort that undertake this route of study, so the student records will change.

• Why are the files named 2011

- yes, I was imagining that it was just as easy to start with an empty db each year than to keep one going indefinitely. Once one cohort has finished we concentrate then on the next cohort. I guess this thinking was also another reason for keeping the placement database separate, as that would be used each year.

Point #21

In order to see which students have been placed at a company, place a portal to the Students table on a layout of Companies. Of course, if you want to also see placements from previous years, you cannot have a separate file for each year

Sorry – I’m not too clear on how this would be mapped out in the design. But I can see that the intention is exactly what I am looking for. So I need to persevere with this.

I hope that answers all the questions. I will go and try again to combine everything into one db. And see where that gets me.

I’ve never said I don’t want to try anyone’s advice, I have however, said I don’t understand. I wonder if some of you wonderful experts forget what’s it’s like ‘not to know’.

Sorry if it has appeared otherwise. I am honestly very grateful for the help I’ve received.

The duplication problem is still somewhat elusive.

Lisa

[lingtheling]

Am looking at it right now... maybe a stupid question, but you're used to me by now maybe...

In Table 'students' I create (serial no) 'student record no' so will be a unique ID. In Table placements I create (serial no) 'placement ID'.

Do I create the relationship between those?

Or

Do I create a 'student record no' also in Table placements and link those. (But I'm not sure why I'd do that).

All the examples I've been looking at have the same ID name in each table.

[comment]

How many placements can a student have? ... If there can be more, you will need a third table for them.

They can have a few, it is typical for them to have one – sometimes three.

Well, you see, this changes the picture considerably - because now you need to use three tables instead of just two. The reason for this is that the relationship between Students and Companies is many-to-many: one student can be placed in many companies, and one company can have many students placed there. You can read more about this here:

http://en.wikipedia.org/wiki/Associative_Entities

Bruce's file already made that assumption. However, I think at this point you'd be better off studying just a bare-bones, no frills demo - see attached.

---

Unrelated, here's a little story: The department of Architecture has decided to build a library. The purpose of the library was twofold:

1. to read books about architectural design;

2. to read books about structural engineering.

Accordingly, they have placed the books about design on the first floor, and the engineering books on the third floor. Of course, it often happened that someone reading a design book came upon a reference to engineering. No problem - they sent him up to the third floor to look it up. They also had two staffs of librarians, and any new book that came in was registered in the index on both floors. Do you see where I am going with this?

Placements.zip

[lingtheling]

Oh for sure... am still working on changing the whole thing... I got stuck on portals last night - but realised after an hr that I hadn't ticked the allow records to be created box - doh! Some things are falling into place - others still a bit elusive. Will be in touch soon.

Thanks :)

[Vaughan]

So what was on the second floor of the library?

[comment]

So what was on the second floor of the library?

That's right.