aother concept I can't get around my head: adding a record if it doesn't 
already exist.

I want to *only conditionally* add a record... e.g. insert soft.. if 
it's "key" (defined at run time) doesn't already exist.

I couldn't figure out how to do this without messing around with found 
sets... I wanted to keep the current found set intact, as it was derived 
from an import, and may not be easy to refind as a group.

what's the accepted idiom to add a record that doesn't already exist, or 
otherwise do nothing?

and yes, I need to populate at least one of the record's fields with a 
global, am I going to have to go through filemaker hell again to do that?

thanks,
B

In article <bob-04A572.16322418112004@shawnews.gv.shawcable.net>, Bob
<bob@nowhere.com> wrote:

> aother concept I can't get around my head: adding a record if it doesn't 
> already exist.
> 
> I want to *only conditionally* add a record... e.g. insert soft.. if 
> it's "key" (defined at run time) doesn't already exist.
> 
> I couldn't figure out how to do this without messing around with found 
> sets... I wanted to keep the current found set intact, as it was derived 
> from an import, and may not be easy to refind as a group.
> 
> what's the accepted idiom to add a record that doesn't already exist, or 
> otherwise do nothing?
> 
> and yes, I need to populate at least one of the record's fields with a 
> global, am I going to have to go through filemaker hell again to do that?

The easiest way to stop duplicates being made is to use FileMaker's
in-built Validation options to say the field must be unique.

Ta-da! The field can no longer have duplicate data.  :o)

Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)

Bob for this one you need to set up a script, and a field or two

CalcField(text) containing all fields you want to search
(FirstName & " " & LastName & " " & Town) etc etc
And a Global which we would name gCurrentSearch(text), put it on a layout

The script I'll just scetch out for you, so you hav e to build one adapted 
to your own wishes. This one for FM5/6. Please allways state version number 
and platform. Makes life a lot easier.

MyScript

Set ErrorTrapping (on)
goto the right layout
goto the gsearchme field
go into find
halt/continue script
set field CalcField=gSearchMe
perform find
if[ currentfoundcount=0]
dialog["Message";"Continue, Search again, Quit";Buttons: "OK"; "Again"; 
"Quit"]
if currentmessagechoise=3
    quit script
else
    if currentmessagechoise=2
        perform Script(MyScript)
        quit script
    else
        if currentmessagechoise=1
            new record
     end if
    end if
   end if
end if



"Bob" <bob@nowhere.com> schreef in bericht 
news:bob-04A572.16322418112004@shawnews.gv.shawcable.net...
> aother concept I can't get around my head: adding a record if it doesn't
> already exist.
>
> I want to *only conditionally* add a record... e.g. insert soft.. if
> it's "key" (defined at run time) doesn't already exist.
>
> I couldn't figure out how to do this without messing around with found
> sets... I wanted to keep the current found set intact, as it was derived
> from an import, and may not be easy to refind as a group.
>
> what's the accepted idiom to add a record that doesn't already exist, or
> otherwise do nothing?
>
> and yes, I need to populate at least one of the record's fields with a
> global, am I going to have to go through filemaker hell again to do that?
>
> thanks,
> B 

In article <191120041917519044%helpful_harry@nom.de.plume.com>,
 Helpful Harry <helpful_harry@nom.de.plume.com> wrote:

> In article <bob-04A572.16322418112004@shawnews.gv.shawcable.net>, Bob
> <bob@nowhere.com> wrote:
> 
> > aother concept I can't get around my head: adding a record if it doesn't 
> > already exist.
> > 
> > I want to *only conditionally* add a record... e.g. insert soft.. if 
> > it's "key" (defined at run time) doesn't already exist.
> > 
> > I couldn't figure out how to do this without messing around with found 
> > sets... I wanted to keep the current found set intact, as it was derived 
> > from an import, and may not be easy to refind as a group.
> > 
> > what's the accepted idiom to add a record that doesn't already exist, or 
> > otherwise do nothing?
> > 
> > and yes, I need to populate at least one of the record's fields with a 
> > global, am I going to have to go through filemaker hell again to do that?
> 
> The easiest way to stop duplicates being made is to use FileMaker's
> in-built Validation options to say the field must be unique.
> 
> Ta-da! The field can no longer have duplicate data.  :o)
> 
> Helpful Harry                   
> Hopefully helping harassed humans happily handle handiwork hardships  ;o)



sorry, that's not helpful.

It's not enough to spit an error when a record is added with non-unique 
data... if anyone on this list knows SQL, it would be a soft insert.

look, this isn't rocket science, it's easy, why can't anyone just give a 
straight answer?:



1) a database exists with a bunch of records
2) add a record (unless it already exists)

.... this is under script control.




how?

Bob <bob@nowhere.com> wrote in message news:<bob-04A572.16322418112004@shawnews.gv.shawcable.net>...
> aother concept I can't get around my head: adding a record if it doesn't 
> already exist.
> 
> I want to *only conditionally* add a record... e.g. insert soft.. if 
> it's "key" (defined at run time) doesn't already exist.

If you know the key, just check for isvalid(relationship::field) 

You can put the key into a global field, and use a self join
relationship with the global field related to the key field in the
same file.

> I couldn't figure out how to do this without messing around with found 
> sets... I wanted to keep the current found set intact, as it was derived 
> from an import, and may not be easy to refind as a group.

This method will not affect your found set.

> what's the accepted idiom to add a record that doesn't already exist, or 
> otherwise do nothing?

I don't know if it's an idiom or not, but it's what I would do.

> and yes, I need to populate at least one of the record's fields with a 
> global, am I going to have to go through filemaker hell again to do that?

I have no idea what you are talking about, but good luck.

In article <bob-6FC51D.02592319112004@shawnews.gv.shawcable.net>, Bob
<bob@nowhere.com> wrote:

> In article <191120041917519044%helpful_harry@nom.de.plume.com>,
>  Helpful Harry <helpful_harry@nom.de.plume.com> wrote:
> 
> > In article <bob-04A572.16322418112004@shawnews.gv.shawcable.net>, Bob
> > <bob@nowhere.com> wrote:
> > 
> > > aother concept I can't get around my head: adding a record if it doesn't 
> > > already exist.
> > > 
> > > I want to *only conditionally* add a record... e.g. insert soft.. if 
> > > it's "key" (defined at run time) doesn't already exist.
> > > 
> > > I couldn't figure out how to do this without messing around with found 
> > > sets... I wanted to keep the current found set intact, as it was derived 
> > > from an import, and may not be easy to refind as a group.
> > > 
> > > what's the accepted idiom to add a record that doesn't already exist, or 
> > > otherwise do nothing?
> > > 
> > > and yes, I need to populate at least one of the record's fields with a 
> > > global, am I going to have to go through filemaker hell again to do that?
> > 
> > The easiest way to stop duplicates being made is to use FileMaker's
> > in-built Validation options to say the field must be unique.
> > 
> > Ta-da! The field can no longer have duplicate data.  :o)
> 
> sorry, that's not helpful.
> 
> It's not enough to spit an error when a record is added with non-unique 
> data... if anyone on this list knows SQL, it would be a soft insert.
> 
> look, this isn't rocket science, it's easy, why can't anyone just give a 
> straight answer?:

Maybe if you asked calmly and nicely and actually explained more
details of what you're trying to achieve you'd get a "straight answer".
But if you're just going to throw temper tantrums and complain about
FileMaker then you can go away and figure it out yourself.



> 1) a database exists with a bunch of records
> 2) add a record (unless it already exists)
> 
> ... this is under script control.
> 
> how?

You can still use the field validation and simply add the record via a
script that traps the error - that way you'll know a record with the
same data in that field exists already.




Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)

In article <5ce548c1.0411190523.2844a11c@posting.google.com>,
 paul@ethicalpaul.com (Paul Bruneau) wrote:

> Bob <bob@nowhere.com> wrote in message 
> news:<bob-04A572.16322418112004@shawnews.gv.shawcable.net>...
> > aother concept I can't get around my head: adding a record if it doesn't 
> > already exist.
> > 
> > I want to *only conditionally* add a record... e.g. insert soft.. if 
> > it's "key" (defined at run time) doesn't already exist.
> 
> If you know the key, just check for isvalid(relationship::field) 
> 
> You can put the key into a global field, and use a self join
> relationship with the global field related to the key field in the
> same file.
> 
> > I couldn't figure out how to do this without messing around with found 
> > sets... I wanted to keep the current found set intact, as it was derived 
> > from an import, and may not be easy to refind as a group.
> 
> This method will not affect your found set.
> 
> > what's the accepted idiom to add a record that doesn't already exist, or 
> > otherwise do nothing?
> 
> I don't know if it's an idiom or not, but it's what I would do.
> 
> > and yes, I need to populate at least one of the record's fields with a 
> > global, am I going to have to go through filemaker hell again to do that?
> 
> I have no idea what you are talking about, but good luck.


so close, but no cigar.

what I'm doing in this case is copying records from one table (the 
sourcetable) to another (the targettable).

so, if someone tries to reload the same file twice (extreme case), it 
won't add any records. if they try to load a file that's 90% the same as 
before, only the 10% get added.

the trouble is, the 'duplicate records' that I need to prevent are based 
on a calculated field in each table:

sourceTable::calcfield == targetTable::calcfield

where:

sourceTable::calcfield [stored] == sourcecategory & sourceproduct
targetTable::calcfield [non-stored] == relatedcategory & 
relatedtargetproduct


I recall that non-stored fields (like calc fields based on related 
fields) don't work as relationships. I don't recall the work around for 
this, though.

in essence, is there a way to constrain a found set to not include 
records in the sourceTable that already have a counterpart in the 
targetTable (i.e. identical category and product field values.) [I 
already constrain the found set already to some degree before adding the 
(hopefully) 'new' records to the targetTable, so this seemed a logical 
opportunity to elimated the duplicates before they get added.) 

does this make sense? have I missed a REALLY obvious way to pull this 
off?

thanks guys, I really do appreciate your help.
B

It may be better to have a temporary Import table identical to the 
target table. Import to that, script it to delete duplicates, and import 
the temp table into the target table.

I think it requires only one relation from the temp to source table. In 
the script (run from the temp table), you basically clear all records, 
import the data, then loop through all of the records doing this:

If(IsValid(RelationToTarget::keyField))
     DeleteRecord
EndIf

At the end of the script, call another script running from targetTable 
to import all from temp table.

If you don't want to delete the records from the temp table (in case 
there is an error in the data), you can omit them instead. A FileMaker 
import will only import the found set of a table if its containing file 
is already open in FileMaker.

Is soft insert supported in all SQL databases? I don't think I've seen 
that command in MySQL or PostgreSQL. I could be wrong though.

In article <cnsuuc$h3p$1@zcars0v6.ca.nortel.com>,
 Kevin Hayes <me@here.com> wrote:

> It may be better to have a temporary Import table identical to the 
> target table. Import to that, script it to delete duplicates, and import 
> the temp table into the target table.
> 
> I think it requires only one relation from the temp to source table. In 
> the script (run from the temp table), you basically clear all records, 
> import the data, then loop through all of the records doing this:
> 
> If(IsValid(RelationToTarget::keyField))
>      DeleteRecord
> EndIf
> 
> At the end of the script, call another script running from targetTable 
> to import all from temp table.
> 
> If you don't want to delete the records from the temp table (in case 
> there is an error in the data), you can omit them instead. A FileMaker 
> import will only import the found set of a table if its containing file 
> is already open in FileMaker.
> 
> Is soft insert supported in all SQL databases? I don't think I've seen 
> that command in MySQL or PostgreSQL. I could be wrong though.
> 


not sure about the soft insert universality (is that a word?!), I spend 
my time in Virtuoso for the most part. 
(http://www.openlinksw.com/virtuoso)

good idea you have, but I'm still not able to put together a valid 
relationship between the (stored) calculated key in the source (raw) 
table and the (unstored) calculated key in the target table, since the 
target table's calculated key (which is based on 2 related fields, each 
in their own related table.)

other than that, your solution is even more elegant than you think... 
the relationship itself can be used to 'view related records', so to 
speak, and make available to the copy script only those 'unique' records.

but, there's that little unstored calculation relationship problem...

arrgh.

any ideas?

thanks,
B

Bob wrote:

> but, there's that little unstored calculation relationship problem...
> 
> any ideas?

Put a tempField (just a textField) in your target table. At the start of 
your import script, put the following

(assuming running from the targetTable)

Show All Records
Go To Field(tempField)
Replace(yourCalcKeyField)


Make the relation from the temporary Table to that tempField instead of 
the calc field. The tempField is a text field so it can be indexed.

In article <cnvsv1$q6f$1@zcars0v6.ca.nortel.com>,
 Kevin Hayes <me@here.com> wrote:

> Bob wrote:
> 
> > but, there's that little unstored calculation relationship problem...
> > 
> > any ideas?
> 
> Put a tempField (just a textField) in your target table. At the start of 
> your import script, put the following
> 
> (assuming running from the targetTable)
> 
> Show All Records
> Go To Field(tempField)
> Replace(yourCalcKeyField)
> 
> 
> Make the relation from the temporary Table to that tempField instead of 
> the calc field. The tempField is a text field so it can be indexed.
> 

clever work around, Kevin, I'll take it.

curious though... isn't this almost a violation of the relationship 
model? shouldn't the relationship always be in context, without having 
to put it into context?

this will do for a prototype, I suppose, but I'd be hesitant to use a 
manually update key in a full application environment, in feat that 
someone else (ok, probably me) would inadvetently believe that 
relationship to be self-sustaining.

does this concern make sense?

like I said, I'll take the work around for now, but I'd be interested in 
hearing your thoughts on this.

thanks,
B

Bob wrote:

> curious though... isn't this almost a violation of the relationship 
> model? shouldn't the relationship always be in context, without having 
> to put it into context?

Since this relationship is only used for a temporary table, then the 
relationship doesn't need to be in context all the time. The data is 
only stored to import, then it can be deleted.

> this will do for a prototype, I suppose, but I'd be hesitant to use a 
> manually update key in a full application environment, in feat that 
> someone else (ok, probably me) would inadvetently believe that 
> relationship to be self-sustaining.

I understand your concern for data that will be stored permanently. But 
if you only allow importing through the script that sets the key, then 
such a restriction would just be arbitrary for data that only exists 
after the keys have been manually set.

> does this concern make sense?

I wouldn't do this for persistent data. But since it's only temporary, 
it should be fine.

> like I said, I'll take the work around for now, but I'd be interested in 
> hearing your thoughts on this.

Sure, a manually set key is ok as long as the data that uses it exists 
only within the same script (or transaction in generic database speak) 
as the settting of the key. So this is ok for my workaround.

Any other time when the data base be used after that it is not ok, which 
is what your concern is.

In article <cnvsv1$q6f$1@zcars0v6.ca.nortel.com>,
 Kevin Hayes <me@here.com> wrote:

> Bob wrote:
> 
> > but, there's that little unstored calculation relationship problem...
> > 
> > any ideas?
> 
> Put a tempField (just a textField) in your target table. At the start of 
> your import script, put the following
> 
> (assuming running from the targetTable)
> 
> Show All Records
> Go To Field(tempField)
> Replace(yourCalcKeyField)
> 
> 
> Make the relation from the temporary Table to that tempField instead of 
> the calc field. The tempField is a text field so it can be indexed.
> 

that worked ducky, thanks!
B

In article <co2nbv$fbk$1@zcars0v6.ca.nortel.com>,
 Kevin Hayes <me@here.com> wrote:

> Bob wrote:
> 
> > curious though... isn't this almost a violation of the relationship 
> > model? shouldn't the relationship always be in context, without having 
> > to put it into context?
> 
> Since this relationship is only used for a temporary table, then the 
> relationship doesn't need to be in context all the time. The data is 
> only stored to import, then it can be deleted.
> 
> > this will do for a prototype, I suppose, but I'd be hesitant to use a 
> > manually update key in a full application environment, in feat that 
> > someone else (ok, probably me) would inadvetently believe that 
> > relationship to be self-sustaining.
> 
> I understand your concern for data that will be stored permanently. But 
> if you only allow importing through the script that sets the key, then 
> such a restriction would just be arbitrary for data that only exists 
> after the keys have been manually set.
> 
> > does this concern make sense?
> 
> I wouldn't do this for persistent data. But since it's only temporary, 
> it should be fine.
> 
> > like I said, I'll take the work around for now, but I'd be interested in 
> > hearing your thoughts on this.
> 
> Sure, a manually set key is ok as long as the data that uses it exists 
> only within the same script (or transaction in generic database speak) 
> as the settting of the key. So this is ok for my workaround.
> 
> Any other time when the data base be used after that it is not ok, which 
> is what your concern is.

well said.

one question, though... is the situation possible where my sctipt would 
set the kwys, but then a moment before I 'use' that key, someone else's 
script could come along and changes the db so that the key is no longer 
correct?

or would this only be a concern if FMP was multi-threaded?

thanks,
B

Bob wrote:

> one question, though... is the situation possible where my sctipt would 
> set the kwys, but then a moment before I 'use' that key, someone else's 
> script could come along and changes the db so that the key is no longer 
> correct?
> 
> or would this only be a concern if FMP was multi-threaded?

FM Server is multithreaded, so that is a good point. Something could be 
done to tag the records that would be changed if modified since the 
script, but I'd have to play around a bit to say exactly what. I'm 
pretty sure it is possible though.

In article <co5cdl$ccs$1@zcars0v6.ca.nortel.com>,
 Kevin Hayes <me@here.com> wrote:

> Bob wrote:
> 
> > one question, though... is the situation possible where my sctipt would 
> > set the kwys, but then a moment before I 'use' that key, someone else's 
> > script could come along and changes the db so that the key is no longer 
> > correct?
> > 
> > or would this only be a concern if FMP was multi-threaded?
> 
> FM Server is multithreaded, so that is a good point. Something could be 
> done to tag the records that would be changed if modified since the 
> script, but I'd have to play around a bit to say exactly what. I'm 
> pretty sure it is possible though.

that's ok, no need to go to the effort... I'm the only one playing with 
the FMP prototype, although I can hear my manager now: "well, just put 
it online" ... ha.

thanks much

In article <191120041917519044%helpful_harry@nom.de.plume.com>,
 Helpful Harry <helpful_harry@nom.de.plume.com> wrote:

> In article <bob-04A572.16322418112004@shawnews.gv.shawcable.net>, Bob
> <bob@nowhere.com> wrote:
> 
> > aother concept I can't get around my head: adding a record if it doesn't 
> > already exist.
> > 
> > I want to *only conditionally* add a record... e.g. insert soft.. if 
> > it's "key" (defined at run time) doesn't already exist.
> > 
> > I couldn't figure out how to do this without messing around with found 
> > sets... I wanted to keep the current found set intact, as it was derived 
> > from an import, and may not be easy to refind as a group.
> > 
> > what's the accepted idiom to add a record that doesn't already exist, or 
> > otherwise do nothing?
> > 
> > and yes, I need to populate at least one of the record's fields with a 
> > global, am I going to have to go through filemaker hell again to do that?
> 
> The easiest way to stop duplicates being made is to use FileMaker's
> in-built Validation options to say the field must be unique.
> 
> Ta-da! The field can no longer have duplicate data.  :o)
> 
> Helpful Harry                   
> Hopefully helping harassed humans happily handle handiwork hardships  ;o)

I see how that works (well!) for keeping records unique by a single key. 
(it's a shame this can't be extended to calculation fields.)

it gets trickier when I required the records to be unique for a 
[non-storable] calculated key, and then even trickier when I needed to 
ensure uniqueness from one table to another (i.e. importing records to a 
temp table, then copying only the unique ones to the 'real' target table.

but, when it's just one field within a single table, the validation 
option of uniqueness works like a charm, thanks.

B