I'm still new to FMP 7 and am fumbling my way through db creation.  I
started with separate files, one for contacts, one for products & image
numbers (they go together to build an end result) and one for
orders/invoices.  I figured I'd could merge them later on but that it might
be easier to have them separate initially.  Any comments would be welcome on
that idea.

My main problem (okay, *today's* main problem) is with the contacts
database.  In a tutorial I watched by Jesse Feiler he recommends having this
db separate (the tut was on 7 btw) and recommends the use of name IDs.  I
have no problem with that outside of not knowing the ids and needing to
either do a find on a name or keep a master list printed somewhere.  My
client, who will be doing some of the order entry, has an issue with it.
She doesn't want to go through those steps.  She wants to just be able to
start typing a last name then first name and have it automatically pop up
the customer information (address/email/phone).

This company will only ever have 3 people with their hands in the database.
Myself as the db designer/bookkeeper/office mgr, the owner and *possibly but
likely not* one employee who handles order shipments.  So the margin for
error w/name entry still exists but since this isn't a large company with
many hands in the database I don't know that the margin is significant
enough to warrant demanded use of Name IDs.

My questions are these:

1. Is there a way I don't know of to use the name IDs but be able to pull
records by typing in the last name/first name and letting the assigned ID
fill itself in along with the customer info?  I tried this a couple of times
but it didn't work for me.  Then again there may have been user error
involved.

2. Will removing the name IDs and using separate fields for last & first
names, then tying on the last name cause FMP 7 to select the most likely
customer and autofill contact info?  I assume so but had better make sure.

3. Should I keep the contacts db as a separate file and then design the
inventory, sales orders & billing invoices (don't really need this as I use
2 accounting pkgs already) as one file that look up the contacts file or
keep them separate as they are now?

Client also wants a TO-DO List type feature to be implemented.  Something
that will prompt her to send this to the printer or follow-up with customer,
or whatever may need to be done in her order process which goes from a photo
shoot session through to shipment of finished products and can span the
course of several months, with follow-up & reminder cards & phone calls
being sent out on a certain schedule.  This particular hurdle is something
I'll try to tackle at a later time.  I don't know if FMP can do that, based
on the above databases I speak of using, whether its just scripting and
learning more about that or what.  But this gives you a general idea of what
we're trying to accomplish and which tools (dbs) we plan to implement to
achieve our goals.


-- 
Tara

Tee wrote:
> I'm still new to FMP 7 and am fumbling my way through db creation.  I
> started with separate files, one for contacts, one for products & image
> numbers (they go together to build an end result) and one for
> orders/invoices.  I figured I'd could merge them later on but that it might
> be easier to have them separate initially.  Any comments would be welcome on
> that idea.
> 
> My main problem (okay, *today's* main problem) is with the contacts
> database.  In a tutorial I watched by Jesse Feiler he recommends having this
> db separate (the tut was on 7 btw) and recommends the use of name IDs.  I
> have no problem with that outside of not knowing the ids and needing to
> either do a find on a name or keep a master list printed somewhere.  My
> client, who will be doing some of the order entry, has an issue with it.
> She doesn't want to go through those steps.  She wants to just be able to
> start typing a last name then first name and have it automatically pop up
> the customer information (address/email/phone).
> 
> This company will only ever have 3 people with their hands in the database.
> Myself as the db designer/bookkeeper/office mgr, the owner and *possibly but
> likely not* one employee who handles order shipments.  So the margin for
> error w/name entry still exists but since this isn't a large company with
> many hands in the database I don't know that the margin is significant
> enough to warrant demanded use of Name IDs.
> 
> My questions are these:
> 
> 1. Is there a way I don't know of to use the name IDs but be able to pull
> records by typing in the last name/first name and letting the assigned ID
> fill itself in along with the customer info?  I tried this a couple of times
> but it didn't work for me.  Then again there may have been user error
> involved.
> 
> 2. Will removing the name IDs and using separate fields for last & first
> names, then tying on the last name cause FMP 7 to select the most likely
> customer and autofill contact info?  I assume so but had better make sure.
> 
> 3. Should I keep the contacts db as a separate file and then design the
> inventory, sales orders & billing invoices (don't really need this as I use
> 2 accounting pkgs already) as one file that look up the contacts file or
> keep them separate as they are now?
> 
> Client also wants a TO-DO List type feature to be implemented.  Something
> that will prompt her to send this to the printer or follow-up with customer,
> or whatever may need to be done in her order process which goes from a photo
> shoot session through to shipment of finished products and can span the
> course of several months, with follow-up & reminder cards & phone calls
> being sent out on a certain schedule.  This particular hurdle is something
> I'll try to tackle at a later time.  I don't know if FMP can do that, based
> on the above databases I speak of using, whether its just scripting and
> learning more about that or what.  But this gives you a general idea of what
> we're trying to accomplish and which tools (dbs) we plan to implement to
> achieve our goals.
> 
> 
as others will no doubt tell you too, ALWAYS ID's.
In FM7 re the typing in names, what happens when their is multiple 
similar names? A rel (lookup) will enter the first. One good way to deal 
with this is to have a pop up window, with a clairvoyant (type ahead) 
portal to list the names, then a button on teh portal row to grab the ID 
back to the main layout field.



additional fields:
g_search1 (global text)
g_search2 (global text calc) = g_search1 & "z"


and a composite rel to the existing NameLast field:
g_search1  <= NameLast
AND g_search2  >= NameLast

use the rel for the portal, add the NameLast and NameFirst fields


If all else (logic, understanding, rationale...) fails, tell your client 
it uses ID's, ID is the absolute fundamental of sound design, End of 
Story. The implementation should make it transparant anyway. For 
example, the portal selection displays names, she clicks on a name row 
to select it, what does she see? The name appear in the main layout; the 
ID can be hidden, use a related name field...


Chris Brown
Neurosurgery
University of Adelaide

in article 2p1s0nFevlo1U1@uni-berlin.de, Tee at crappolagozhere@netscape.net
wrote on 25/8/04 7:58 AM:

> 1. Is there a way I don't know of to use the name IDs but be able to pull
> records by typing in the last name/first name and letting the assigned ID
> fill itself in along with the customer info?  I tried this a couple of times
> but it didn't work for me.  Then again there may have been user error
> involved.

Always, always, always use name IDs rather than names for the match field of
all major relationships to the contacts table.  Why?  Because names are
changed (through marriage, divorce, choice, misspelling and correction, and
so on) and when a name is changed in one file it does not automatically
update in related records in other tables.  Name IDs, on the other hand, are
just dumb numbers that never need to be changed.
> 
> 2. Will removing the name IDs and using separate fields for last & first
> names, then tying on the last name cause FMP 7 to select the most likely
> customer and autofill contact info?  I assume so but had better make sure.

First of all, make first and last name separate fields then use a calc to
concatenate them they way you want them to appear in a popup list (for
example, surname then a comma then first name.  Use the calc for all
references to the name field below.

First method gives you more or less what you and your people seem to be
asking for, but not without problems (see below):

Create two relationships between contacts and each other table that uses
contacts, one based on name (used only for initial creation of related
record) and one based on name ID (this will be the main relationship).

Create a lookup for the name ID field for each related table that looks up
the name ID based on name (you can do the same thing for any other fields
you need in the related table, eg address, telephone, if you need a
permanent record of these to maintain the historical accuracy of the
document created).

Format the name field as a popup list that references the name field in the
contacts file.  When a user clicks into this field, the popup list will
display.  You can use the arrow keys or mouse to navigate the list, or you
can rapidly type the first few letter to jump to that position in the list,
then hit enter to enter the selected value.  Other values, including name ID
will autoenter based on lookups.

The problem here is that there can be more than one contact with the same
first and second name (and you might be surprised to know how often this
happens).  If you sort the name value list by name, only the first record
for that name will show unless you take extra measures.  Needless to say,
this would create enormous problems.  And sorting by the ID field, while
ensuring uniqueness, will throw the names out of alphabetical order and
remove the type ahead feature.

Bullet proof method:

When selecting a name for a related record (an order or an invoice, for
example) place the name ID field, formatted as a popup list that references
both the name ID AND the name field (sort by name).  When the user create a
new record, the popup list will show all name Ids with the names next to
them in alphabetical order to make selection easier.  Upon selection, only
the ID will be entered.  For other fields (name, address, telephone, etc),
you can either use related fields or calc fields that reference those field
or lookups (lookups are fields that autoenter a value based on a
relationship, in this case the name ID relationship) depending on need.

NB Typing the first few letters doesn't seem to work in FM7 with popup lists
set up in this manner, I don't know why.

You can also build a reasonable approximation of a type ahead feature that
reduce a portal to only contacts beginning with the first 1, 2 or 3 letters
typed in a filter field.  This is easier than ever if you have FMDev7 (using
custom functions) but also possible without.  Then make a scripted button
for the portal that sets the name ID field to the name ID of whichever
contact is selected from the portal.
> 
> 3. Should I keep the contacts db as a separate file and then design the
> inventory, sales orders & billing invoices (don't really need this as I use
> 2 accounting pkgs already) as one file that look up the contacts file or
> keep them separate as they are now?

This is a tough one.  You can now build a complete solution within a single
file, or have a separate interface file and a separate data file (well,
almost) or several data files, or have all table representing discrete
modules within separate files. Or any combination of the above. How you do
it depends on the size of the solution, the way in which it is going to be
used, who is going to use it, who is going to upgrade it, and so on.  Its
all too new for any of us to have a definitive answer as yet.
> 
> Client also wants a TO-DO List type feature to be implemented.  Something
> that will prompt her to send this to the printer or follow-up with customer,
> or whatever may need to be done in her order process which goes from a photo
> shoot session through to shipment of finished products and can span the
> course of several months, with follow-up & reminder cards & phone calls
> being sent out on a certain schedule.  This particular hurdle is something
> I'll try to tackle at a later time.  I don't know if FMP can do that, based
> on the above databases I speak of using, whether its just scripting and
> learning more about that or what.  But this gives you a general idea of what
> we're trying to accomplish and which tools (dbs) we plan to implement to
> achieve our goals.

Filemaker can handle this.  I recently built a full featured calendar
solution (complete with day, week, and month views) that uses the startup
script (with dialog for entry of user password) to check for items that are
due for that use on that day. As an optional extra, I included scripts that
notify other staff members of new calendar items (delegation is a wonderful
thing) and that export items to iCal (Mac only) to take advantage of iCal's
more robust reminder features (ie timed to the minute rather than the day).

Bridget Eley

-- 

(to email direct, replace "DOT" with "." and remove ".invalid")

Chris, thank you for the detailed response.  Your field descriptions are in
a foreign language to me right now because I haven't gotten that far in my
learning of the program (I just learned about value lists but haven't done
any hands-on experimenting with them).  So far I've been using the absolute
basic features but I do understand the concept you put forth.

-- 
Tara

"Bridget Eley" <bridgeteley@ihugDOTcomDOTau.invalid> wrote in message
news:BD521B78.690B%bridgeteley@ihugDOTcomDOTau.invalid...
> in article 2p1s0nFevlo1U1@uni-berlin.de, Tee at
crappolagozhere@netscape.net
> wrote on 25/8/04 7:58 AM:
>
> > 1. Is there a way I don't know of to use the name IDs but be able to
pull
> > records by typing in the last name/first name and letting the assigned
ID
> > fill itself in along with the customer info?  I tried this a couple of
times
> > but it didn't work for me.  Then again there may have been user error
> > involved.
>
> Always, always, always use name IDs rather than names for the match field
of
> all major relationships to the contacts table.  Why?  Because names are
> changed (through marriage, divorce, choice, misspelling and correction,
and
> so on) and when a name is changed in one file it does not automatically
> update in related records in other tables.  Name IDs, on the other hand,
are
> just dumb numbers that never need to be changed.

Yes, that was my understanding as well but figured I'd ask so that when I'm
questioned about it then I'd have more backup so to speak.

> > 2. Will removing the name IDs and using separate fields for last & first
> > names, then tying on the last name cause FMP 7 to select the most likely
> > customer and autofill contact info?  I assume so but had better make
sure.
>
> First of all, make first and last name separate fields then use a calc to
> concatenate them they way you want them to appear in a popup list (for
> example, surname then a comma then first name.  Use the calc for all
> references to the name field below.
>
> First method gives you more or less what you and your people seem to be
> asking for, but not without problems (see below):
>
> Create two relationships between contacts and each other table that uses
> contacts, one based on name (used only for initial creation of related
> record) and one based on name ID (this will be the main relationship).
>
> Create a lookup for the name ID field for each related table that looks up
> the name ID based on name (you can do the same thing for any other fields
> you need in the related table, eg address, telephone, if you need a
> permanent record of these to maintain the historical accuracy of the
> document created).
>
> Format the name field as a popup list that references the name field in
the
> contacts file.  When a user clicks into this field, the popup list will
> display.  You can use the arrow keys or mouse to navigate the list, or you
> can rapidly type the first few letter to jump to that position in the
list,
> then hit enter to enter the selected value.  Other values, including name
ID
> will autoenter based on lookups.
>
> The problem here is that there can be more than one contact with the same
> first and second name (and you might be surprised to know how often this
> happens).  If you sort the name value list by name, only the first record
> for that name will show unless you take extra measures.  Needless to say,
> this would create enormous problems.  And sorting by the ID field, while
> ensuring uniqueness, will throw the names out of alphabetical order and
> remove the type ahead feature.
>
> Bullet proof method:
>
> When selecting a name for a related record (an order or an invoice, for
> example) place the name ID field, formatted as a popup list that
references
> both the name ID AND the name field (sort by name).  When the user create
a
> new record, the popup list will show all name Ids with the names next to
> them in alphabetical order to make selection easier.  Upon selection, only
> the ID will be entered.  For other fields (name, address, telephone, etc),
> you can either use related fields or calc fields that reference those
field
> or lookups (lookups are fields that autoenter a value based on a
> relationship, in this case the name ID relationship) depending on need.
>
> NB Typing the first few letters doesn't seem to work in FM7 with popup
lists
> set up in this manner, I don't know why.
>
> You can also build a reasonable approximation of a type ahead feature that
> reduce a portal to only contacts beginning with the first 1, 2 or 3
letters
> typed in a filter field.  This is easier than ever if you have FMDev7
(using
> custom functions) but also possible without.  Then make a scripted button
> for the portal that sets the name ID field to the name ID of whichever
> contact is selected from the portal.

This is alot to absorb and much of it is still foreign & untried to me but
having watched a training video, the method names are familiar so I'll just
need to rewatch then try the example myself.  Figuring out what is needed
(ie calc field, portals, global, etc) is a bit out of my reach at the moment
because I don't know enough about these features to know how they work or
why I should use them.  I hate to burden the ng with questions & lengthy
posts but this is an excellent way for me to learn these things so I really
appreciate you taking the time to respond in such detail.


> > 3. Should I keep the contacts db as a separate file and then design the
> > inventory, sales orders & billing invoices (don't really need this as I
use
> > 2 accounting pkgs already) as one file that look up the contacts file or
> > keep them separate as they are now?
>
> This is a tough one.  You can now build a complete solution within a
single
> file, or have a separate interface file and a separate data file (well,
> almost) or several data files, or have all table representing discrete
> modules within separate files. Or any combination of the above. How you do
> it depends on the size of the solution, the way in which it is going to be
> used, who is going to use it, who is going to upgrade it, and so on.  Its
> all too new for any of us to have a definitive answer as yet.

See this is my concern (the newness of FMP 7 compiling all info in one
place) as well and why I chose to initially create separate files.  At worst
I have to create relationships to dbs in other files but at best, should
something happen to one file, the entire database isn't destroyed.  I like
having everything concentrated but with the amount of work I'm putting into
this, particularly given the learning-as-I-go factor, I am hesitant to put
all my eggs in the same basket...or file in this case.


> > Client also wants a TO-DO List type feature to be implemented.
Something
> > that will prompt her to send this to the printer or follow-up with
customer,
> > or whatever may need to be done in her order process which goes from a
photo
> > shoot session through to shipment of finished products and can span the
> > course of several months, with follow-up & reminder cards & phone calls
> > being sent out on a certain schedule.  This particular hurdle is
something
> > I'll try to tackle at a later time.  I don't know if FMP can do that,
based
> > on the above databases I speak of using, whether its just scripting and
> > learning more about that or what.  But this gives you a general idea of
what
> > we're trying to accomplish and which tools (dbs) we plan to implement to
> > achieve our goals.
>
> Filemaker can handle this.  I recently built a full featured calendar
> solution (complete with day, week, and month views) that uses the startup
> script (with dialog for entry of user password) to check for items that
are
> due for that use on that day. As an optional extra, I included scripts
that
> notify other staff members of new calendar items (delegation is a
wonderful
> thing) and that export items to iCal (Mac only) to take advantage of
iCal's
> more robust reminder features (ie timed to the minute rather than the
day).

Oh my, do you hire yourself out as a consultant by any chance?  :)

Thanks again, your reply clarified alot, left me with more questions (which
is good because I need to know these things that many others consider
basic), but helped get me on track with where I need to go from here.

-- 
Tara

The best way to guard against db loss due to corruption or whathaveyou is
the same now as it ever was:

1. never develop on files that are in use
2. backup developer files regularly during development process (every few
hours at least)
3. trash developer files if the you experience a crash during the
development process (ie in layout mode, in field defs or in scriptmaker))
and revert to the last backup (which was made during the last few hours so
loss should be minimal)
4. backup the live files regularly (at least once a day)

If you do this, loss due to corruption should not be an issue regardless of
the number of files you create.  If the live file(s) become corrupt, you can
revert to yesterday's backup.  If the db itself is glitching, import the
data  into a copy of the most recent version of the developer copy.

Bridget Eley

in article 2p2gc4Ffsj10U1@uni-berlin.de, Tee at crappolagozhere@netscape.net
wrote on 25/8/04 1:46 PM:

> See this is my concern (the newness of FMP 7 compiling all info in one
> place) as well and why I chose to initially create separate files.  At worst
> I have to create relationships to dbs in other files but at best, should
> something happen to one file, the entire database isn't destroyed.  I like
> having everything concentrated but with the amount of work I'm putting into
> this, particularly given the learning-as-I-go factor, I am hesitant to put
> all my eggs in the same basket...or file in this case.

-- 

(to email direct, replace "DOT" with "." and remove ".invalid")

Tee wrote:
> Chris, thank you for the detailed response.  Your field descriptions are in
> a foreign language to me right now because I haven't gotten that far in my
> learning of the program (I just learned about value lists but haven't done
> any hands-on experimenting with them).  So far I've been using the absolute
> basic features but I do understand the concept you put forth.
> 


Hi Tara,

given your  newness to FM, I would suggest you make a QAD (quick and 
dirty) database, to invesigate the methods. Nothing flash just the few 
basic fields to emulate what you want to implement in the existing solution.
Something that  is simple, usually makes things obvious/easier to 
understand. Plus it doesn't matter if you wreck it (you won't, FM is 
very robust).  If you want to walk through such an excercise, no problem.


regards

Chris

On 2004-08-24 19:33:28 -0500, Bridget Eley 
<bridgeteley@ihugDOTcomDOTau.invalid> said:

> in article 2p1s0nFevlo1U1@uni-berlin.de, Tee at crappolagozhere@netscape.net
> wrote on 25/8/04 7:58 AM:
>> 
> 
> Filemaker can handle this.  I recently built a full featured calendar
> solution (complete with day, week, and month views) that uses the startup
> script (with dialog for entry of user password) to check for items that are
> due for that use on that day. As an optional extra, I included scripts that
> notify other staff members of new calendar items (delegation is a wonderful
> thing) and that export items to iCal (Mac only) to take advantage of iCal's
> more robust reminder features (ie timed to the minute rather than the day).
> 
> Bridget Eley

I am trying to export a tickle (reminder) database I created to Ical 
but can't figure out the right scripts.  I'm sure applescript would be 
involved.  I have a database that I can set the a reminder date with 
note and reference to client//matter.  I would like a scriptable button 
that would send that info to ical.  Any guidance?

I originally tried using applescript  (there was an how-to article in
Macworld earlier in the year) but my knowledge of applescript was not
sophisticated enough to be able to adapt the solution to my specific needs -
basically, I wanted to be able to set up every iCal variable within
filemaker, so that there was no additional need to fix appointments and
tasks once imported into iCal.

I finally decided to go with a rather novel approach made available by
Cleveland Consulting Inc which basically exports data in the vcalendar
format that iCal and many other calendering programs use.  Basically, what
happens is that filemaker is used to generate the vcalendar code line by
line (using lots of loops and global field variables), then saves it as a
file with an .ics suffix which can then be imported into iCal. Their free
download is very basic, but it will give you the general idea and vcalendar
is much easier to learn and adapt than applescript!

You can download their version 6 demo here:

<http://www.clevelandconsulting.com/cp-app/prod/iCalFilter>

When upgrading the file to v7, you may have to fix some of the scripts to
get it working again, but the basic principles and method are the same.

Tip: if you want to do something that the Cleveland Consulting solution
doesn't do, do it in iCal first, export the data to your desktop, open it in
a text editor and see how iCal handles it.  It is then a relatively easy
matter to see how you need to adapt the filemaker scripts to produce it.

Bridget Eley

in article 2004082909595316807%jr@nullcom, jr at jr@null.com wrote on
30/8/04 12:59 AM:

> On 2004-08-24 19:33:28 -0500, Bridget Eley
> <bridgeteley@ihugDOTcomDOTau.invalid> said:
> 
>> in article 2p1s0nFevlo1U1@uni-berlin.de, Tee at crappolagozhere@netscape.net
>> wrote on 25/8/04 7:58 AM:
>>> 
>> 
>> Filemaker can handle this.  I recently built a full featured calendar
>> solution (complete with day, week, and month views) that uses the startup
>> script (with dialog for entry of user password) to check for items that are
>> due for that use on that day. As an optional extra, I included scripts that
>> notify other staff members of new calendar items (delegation is a wonderful
>> thing) and that export items to iCal (Mac only) to take advantage of iCal's
>> more robust reminder features (ie timed to the minute rather than the day).
>> 
>> Bridget Eley
> 
> I am trying to export a tickle (reminder) database I created to Ical
> but can't figure out the right scripts.  I'm sure applescript would be
> involved.  I have a database that I can set the a reminder date with
> note and reference to client//matter.  I would like a scriptable button
> that would send that info to ical.  Any guidance?
> 

-- 

(to email direct, replace "DOT" with "." and remove ".invalid")