Notes Adding

[wrigley]

I seem to have come across a problem, I am attempting to create a customer contact notes system.

The fields we will have will be 'reason for call', 'notes', 'client id' and 'date'. What I dont seem to understand is how I get to have a single line of notes, then once that has been completed for the program to have another empty fields line created, is this possible. If not is it possible to have a large number of available columns within the database, what I dont want is to have loads of columns and having the user to tab through all of them.

Is there a good way to sort this?

Cheers in advance

[Fenton]

Notes should be its own table, related by client id. They should show in a portal. "New Note" should be created by script. The portal (or relationship) should be sorted descending by a Notes timestamp field, auto-enter creation timestamp (also used for display and searches). That way the most recent note is at the top of the portal. New note script:

Freeze Window

Enter Browse Mode []

Set Variable [ $client_id; Client::client id ]

Go to layout [ Notes ]

New Record

Set Field [ Notes::Client Id; $client_id ]

Commit Record

Go to layout [ Original ]

Go to Field [ Notes::Note ]

Go to Portal Row [ First ]

(P.S. To format a timestamp field (get rid of the seconds) you need to format BOTH as a Date and as Time; then it will stick.)

Edited August 23, 2007 by Guest

[wrigley]

Thanks for this, I seem to be getting my head around it, I do have a problem in that when I try to create a 'portal' I can select from 'manage database' and after that it stays greyed out. Any ideas on this?

As I am a complete Newbie at this how do I get to have the notes table shown within the same window as the other client information, I have edited the fields and the layout but at present they are on different pages. Also is there a way to do a master page for pages like - add record / find record / delete record / edit record / run query etc - I know you can do the action buttons but I dont seem to be able to see the way to set a heirarchy on the pages.

[Fenton]

Hmm, I see you really mean "beginner" :-] I'm afraid this is going to be a little bit beyond you. But it is not all that complex. First you must understand what a "relationship" is. Look at the Relationship Graph, which is one of the tabs after "Manage -> Database". The two tables each have a instance (the correct term is Table Occurrence, TO for short). The line between them, with its little white box, is a relationship. That's what ties TOs together. In this case they are tied with the field "ClientID".

ClientID is an auto-enter serial ID in Client; the most important field in the table, the anchor and target of relationships to/from Clients. Look at its Options. It can be either number or text, but I used text (for no real reason).

Once you have a relationship you can, in Layout mode, create a portal based on it, using the portal tool (looks like a portal, lines across a rectangle). It will ask you what TO. Since you know you are starting from the Client layout (important), you want choose the connected Notes TO, which is named "cl_Notes" (according to my naming convention).

Then you choose the fields for the portal, TS_created (timestamp) and Note (text). Another option of the portal is whether to Sort it. So yeah, we want to sort it by TS_created, descending. That way the newest ends up on top. Then we need a script to create a new portal row for a note. And a button to run the script.

I added a couple more do-das. A button to delete a row. And a button to view the note in its full-size Note layout. This can be done fancier by creating new window instead; but that's beyond beginner for sure (mostly 'cause you have to control the windows).

Notes_simple.fp7.zip

[wrigley]

Hi there,

Thanks for the feedback it has given me a much better understanding where I am going with this, could you possibly check over what I have done so far this afternoon.

I have set up the tables and have attempted to create the relationships but possibly may have done this incorrectly.

Essentially the aim is that for each client that is signed up through one of our estate agent affiliates that we will have the agency it has come from and also the individual sales agent also, thus enabling us to run a query eventually for all sales by individual sales advisors or by estate agency. The other aim is that we can have a concise breakdown of all clients, their details, claim details and the notes relating to their claim.

If you could give me some pointers of where I am going wrong that would be great.

Cheers again for your time

RDM1.zip

[Fenton]

Basically, you are not trusting your relationships. Each table should have 1 and only 1 primary serial ID, unless it is a subsidiary table such as Notes, which doesn't really need one. RDM (?) perhaps doesn't either, as it's a combination. But it should have one, for internal use if nothing else.

Since each table has a reliable primary ID the relationships do not need anything else. A relationship will usually be based on 1 or more IDs (depending). Sometimes it may have addition criteria, such as a date range, etc.. What it will NOT have is a bunch of user-entered data fields, like names, addresses, etc.. This defeats the entire purpose of using IDs. A misspelling in any one of the user data fields will break the relationship, which is bad.

The ID is choosen from a drop-down value list, in this case ClientIDs. I've set the value list options to show ONLY the last, first name calculation. This looks good, but will break if there are EVER 2 people with the same first & last names. Be cautious. You might want to include a middle initial in the table and calculation to stop that happening.

Another possible missunderstanding is that all the data has to exist in every table. Once you have the ID you can see its related data in its base table; Client info is available in the Client Details table. If it exists anywhere else also, then it is known as "redundant" data.

Sometimes however you want redundant data, for a historical record, Finds in the local table, etc.. In that case you can use a Lookup to bring it over from its base table. This is done via the ID relationship to its base table, and happens automatically upon choosing the ClientID (in this case). You do NOT type it over again.

Since I don't really know which you want I left it pretty much as is, which is inadequate. It is difficult for me to makes decisions about someone else's business, when I know little about it. I can only point out what seems wrong, or needs a decision made.

As far as Notes go, they are their own table, they are NOT a field in RDM. Only the ClientID is in RDM. A script is required to create a new note. You didn't transfer it; it needed to fixed after transfer anyway.

You do not have to use my relational method for Notes. You can just do it as a flat field in RDM. But the related field can be searched by date, which makes a big difference.

RDM_fej.fp7.zip