If a record doesnt exist

[C.I.D.E.R]

I have a table that I enter data into and one of the fields is a registration number.

What I would like to happen is, if a registration doesnt exist it then opens up a window with the registration entry on. Is this possible ?

Edited April 7, 2008 by Guest

[LaRetta]

It sounds like you want the ability to find the Registration if it exists and, if not, create a new record with that registration number in it. Yes, quite possible using a global text field to hold the 'registration number' maybe call the field gRegistration (type global) and script might be something like:

[color:gray]User types into gRegistration and hits FIND button) ...

Enter Find Mode [ ] [color:gray]... uncheck pause

Set Field [ Registration Number ; gRegistration ]

Set Error Capture [ On ]

Perform Find [ ]

If [ not Get ( FoundCount ) ]

Show Customer Dialog [ "Yes" "No" ; "Registration " & gRegistration & " not found. Add new one?" ]

If [ Get ( LastMessageChoice ) = 1 ]

New Record Request

Set Field [ Registration ; gRegistration ]

Else

[color:gray]# decide whether you want to blank gRegistration here or let User see what they typed. Usually they will want to see it, because they probably mistyped and they expected to FIND an existing Registration. By leaving it there, it is easier for them to modify it.

Exit Script

End If

[color:gray]# Registration found (or new one created) now go to it

[color:red]New Window

Go To Layout [ Registration Form ]

Set Field [ gRegistration ; "" ]

End If

UPDATE: BTW, your subject should probably be 'If RECORD doesn't exist', not FILE. If so, you might correct your subject; otherwise my script is probably wayyyyyyy off. :smile2:

Edited April 6, 2008 by Guest

[C.I.D.E.R]

OK record not file sorry....

I have tried doing what you have put but due to my inexperience with FM i am having trouble entering what you have typed.

can i type it in a text editor then import the script ?

[LaRetta]

No, FM won't import text into their scripts but, if you give me the table occurrence name and exact field name for the registration number, I will create the script in a new file and give it to you. :laugh2:

[C.I.D.E.R]

Thank you... I am working very hard to learn FM I used to design websites and use MYSQL dbs but I have changed to FM developing. well trying to learn to develop.

The TO is just called "jobsheets"

The field is called " registration"

not too far from what you have already typed.

Thanks again...

Do you fancy doing some intercontinental tuition ?

[LaRetta]

Hi C.I.D.E.R,

Problem: How to find an existing Registatration, select from several if only part of the number is entered (for lazier Users, smile), and if no registration found, gives User opportunity to create new record ... all in one whack.

This demo file shows the script I outlined above. Create a global field called gRegistration and place on Find layout. What this demo does (more specifically):

1 - makes sure something is entered into global otherwise stops.

2 - if only one match, opens Registration Window to it.

3 - if no match, asks User if they want to create new Registration

if yes, new registration is created and opens Registration Window.

if no, cancels attempt.

4 - if more than one registration found, (ie, User types 36 and gets

3 resulting registrations) then list view is presented so User can select correct

registration. Then Registration Window opens on selected registration.

Let me know if you need further direction on it but if you pick this apart (and even just import the scripts into your file) then you should be fine.

LaRetta :wink2:

jobsheets.zip

[C.I.D.E.R]

thank you for your help. What you have done is what I am looking for. I have tried importing what you have done and recreating it within what I have done so far. but.....it doesnt work.

please could you have a look at what I have done and point me in the right direction.

garagepro__2_.zip

[LaRetta]

When you open the first script (Find or Create Registration), you will see this in the first line:

/*IsEmpty ( jobsheets::gRegistration )*/

FileMaker is telling you that it cannot find gRegistration as a field in Jobsheets. There is a gRegistration there NOW but there wasn't when you imported. You forgot to create gRegistration first didn't you.

So it broke your script. So you then changed your Registration field to be gRegistration (but left it as a standard text field). You need your Registration field in there because otherwise, what field would FileMaker be searching? Those broken scripts won't fix themselves now. Change gRegistration back to Registration. Create a NEW field gRegistration (and this time, be sure to go to its field OPTIONS, click the Storage tab and check 'use global storage' at the top.

Delete out all the scripts you imported and import them again ... I'd suggest you just correct them but you might miss one. Nonetheless, it is good practice. Find that first one and delete at the /* at the beginning and */ at the end. You will see that FileMaker won't squak and will accept the new syntax. Now that you understand how to break a script but not having the proper fields in place FIRST, go ahead and delete those scripts and try it again. Let us know how you go with it. :wink2:

[LaRetta]

I also wanted to let you know - you may not have gotten off to such a bad start ... your first script attempt 'reg doesnt exist' (the one you started to mimic my typed example above) broke because you were using a script-step called Perform Find/Replace and you needed to use Perform Find step instead. Those two script-steps are confused by MANY people.

Just look at all the wonderful things you've learned just from this one process!! FileMaker is NEVER boring, that's for sure!

LaRetta :smile2:

[C.I.D.E.R]

ok,

i have doen what you said.

created gRegistration.

re-imported scripts

I haven't changed anything else

Now, I can create a new record, if no reg is entered when I click find, it does the first part of the script.

It does the Registration not found create new one.

After this it goes a bit pear shaped.

If a Registration is in the system, what I would like to happen is fill the reg into The regisration number in so that I can continue and make a new jobsheet.

If one isnt in the system then I would like the script to take me to the vehicle entry layout so that I can create a new vehicle, once that is done I want to press a save button and then go back to the jobsheets page and fill in the registration number.

I have tried changing a couple of the lines but still not working.

By the way I really do appreciate your help..

I have enclosed my updated file.

garagepro.zip

[LaRetta]

I've been thinking about your structure and situation. Before I resolve this for you, I have a few more questions which will alter how I modify the script:

1) Why are you also putting Make and Model in your Jobsheets table? Usually you would put a VehicleID field in jobsheets. Then make and model are available to place on your jobsheets layouts from Vehicle without duplicating the data and even the Registration doesn't need to be duplicated!!

2) Is it true that you can have many jobsheets on the same Registration? So what you want to do is:

a) Search for a Registration [color:green](in the Vehciles table)

: If it doesn't exist in VEHICLES then create a new vehicle in Vehicles with that global registration number.

c) then use that newly created vehicle record to populate your existing (new) jobsheets record.

I originally set you up to search jobsheets and insert Registration in jobsheets since Registration existed there. Please explain the purpose of Vehicles table. It feels like you might be missing a table here. Can there be many Ford Bronco records in Vehicles, each with a different Registration number? So vehicles is the fleet and NOT a make/model table with one each of every vehicle TYPE. [color:green]And there can be many Jobsheets per vehicle. Please clarify.

After I'm clear on these issues, I'll finish correcting your script accordingly if you wish it.

LaRetta

Edited April 11, 2008 by Guest
Clarified green

[C.I.D.E.R]

ok, I have now got to a point where it works.

I have enclosed it for you to have a look at.

I have now got a new problem, On the find page I have got a portal showing all jobcards. Well I would like all the job cards in one portal. but what is happening is each job card is having a seperate find page ?

garagepro.zip

[C.I.D.E.R]

Now jobsheets should be called "vehicles" I know how to put fields on my jobcards that contain lookup data form the vehicles TO

Yes, each registration could have 100's of jobcards

I have knd of got it working now, the problem that I am now having is that On the find page, I would like to dislay all of the jobcards descending in jobcard ID order. newest jobcard at the top.

[LaRetta]

Well, you keep changing everything; and changing where we look up Registration; and even the table names and I simply can't help you if I have no clarity of what you want/need. I have spent time tearing a file of yours apart and getting ready to respond only to have you again change.

If you wish, please answer EACH specific question I have asked and give me a CLEAR view of exactly what you want to achieve, I shall help you through it. So lets back up and now that you've gotten a better picture of your needs, present it all over again ... one step at a time ... specifically what you want. Because, even though you tell me in this last post what you want, I don't know any longer how you want to get it. For instance, do you still want the Vehicle Registration to pop up as an overlapping window so you can enter new Vehicles?

Anyway, I'm too lost to help you. Re-paint a new view for me and I'll help you get exactly what you want. Sound fair? :wink2:

[C.I.D.E.R]

OK, I am sorry for the confusion.

I will type out my project into a doc and then post here. can i email you a username and password ?

[Peter W]

I know this is dated, but LaRetta posted a solution I was seeking, namely, a script to find a record and if not found add. I downloaded the example, garagepro.zip (I don't know what the original poster was attempting, but garagepro is excellent). I then modified this to be a class registration DB. I changed the global to gName rather than registration number. If a student's name was not in the database, I could create it. Okay, that works very well and teaches me the script functions. 

I then added a Class table, (ClassID, TopicID) and a join table, Class Registrations (fk_Name, fk_Topic). So, I could search for a student and if not found, register a student and add them to one or more classes in the Class Registration table. I made the classes to be a pull down from the related Class table. I have the relationships set to allow creation of a new record in the Class table (or to the Class Registration table). 

My question is, although I have the relationship set to allow addition of new records to the Class table, I cannot add a new class not found in the pull down list of classes in the registration form (Class Registration portal). I hope I have described this clearly. How can I add a new Topic to the related Class table from the Class Registration portal (if it isn't already present)?

[comment]

You cannot - not directly. You need an [Add Topic…] button that will run a script to add a new record to the Topics table (independent of the current context), and return back to where you were. 

This really should be a new thread.

 

[Peter W]

Okay, I understand.