looking up a field in another table during data entry

[christw]

Hi, I need some help on how to set up a script. First let me explain what I need. I don't know if anyone has ever used Quickbooks Accounting. When you enter bills in this software, the first field you have to fill is the supplier name. It has an autocomplete functionality so inputting is quite easy if the supplier is already set up in the system. If while entering a bill you come over a new supplier, you just type in the full name and as soon as you tab out of the supplier field, the system informs you that this is a new supplier and asks you whether you want to "Quick add", If you accept, it creates a new supplier in the suppliers list and you may continue with the inputting of the rest of the bill details.

 

I want to implement the same functionality although slightly different in context. My program will be used to issue acceptance certificates for stocks received at work. While in most cases, the items would already be in the system, there may be cases when we add new items and I wanted the same functionality. For the tabbing out of the field I will use either zippscript or eventscript. However, I first have to set up the correct script and that's where I'm stuck.

 

 

Certificate Table ........Item Table

Certificate No

Item Code -----------> Item Code

Item Description

 

To test the script for now I have placed a button on the layout and trigger it myself once I have entered or changed the item code. In my opinion the script should copy the entered item code into a variable, switch to the item layout, perform a find for the item. If it exists just return to the original layout and goto the next field. If it doesn't pop up a question on whether you want to enter a new item. After entering the new item, the user is switched to the original layout and into the next field. The problem is that as soon as I switch layout, the current record must either be saved or lost. I tried using a new window but couldn't figure it out either.

 

Can anyone help?

 

Chris

[Techphan]

I did this exact thing with a DOS database 1 years ago (Clarion 2.1) but have not had a need in FM (yet). I will write out the script that I think will work and then rely upon one of the more experienced developers to polish it up:

 

For this example I will use tables Supplier and Item. Have an entry form (Global::SupplierName) with a button underneath to activate the script.

 

 

Go to Layout(Supplier)

Show all records

Enter find mode

Set field (Supplier::Name; Global::SupplierName)

Set Error Capture (On)

Perform Find

Set Error Capture(off)

If (Get (LastErrorCode)=401)

....Go to Layout(Supplier)

....New Record/Request

....Set field (Supplier::Name;Global::SupplierName) ##(Comment: assumes Supplier::pKey automatically set by FM)##

....Go to next field ##(Comment: for the rest of Supplier form input)##

End If

Set variable ($$Sup_pKey; Supplier::pKey)

Set variable ($$Sup_Name; Supplier::Name) ##(Comment: just in case you had to choose between several Suppliers that had the same or close names)##

Go to Layout (Item)

New Record/Request

Set field(Item::Name;$$Sup_Name)

Set field(Item::fKey; $$Sup_pKey)

Set field(Item::Date;Get(CurrentDate)

Go to next field

##(Comment: script leaves you in the Item form to fill out anything else that is necessary)

[christw]

Thanks TechPhan for the reply.

 

I have found that the following works great and does not cause the screen to flicker like it would if I didn't create a new window.

 

Anyone interested may try it out.

 

Set Error Capture [ On ]

Set Variable [ $Item; Value:Certificates::Item No]

New Window [ Name: "New Win"; Height: 100; Width: 100; Top: 0; Left: -120 ]

Go to Layout [ “Items” (Items) ]

Enter Find Mode [ ]

Set Field [ Items::Item No; $Item ]

Perform Find [ ]

If [ Get ( FoundCount ) = 0 ]

...Show Custom Dialog [ Title: "New item Entered"; Message: "The item code you have entered is not associated with an item

description. Would you like to create a new item?"; Buttons: “Yes”, “No” ]

...If [ Get ( LastMessageChoice ) = 1 ]

......Close Window [ Current Window ]

......Show Custom Dialog [ Title: "Create new item"; Message: "The routine goes here"; Buttons: “OK”, “Cancel” ]

...Else

......Close Window [ Current Window ]

......Go to Field [ Select/perform; Certificates::Item No ]

...End If

Else

...Close Window [ Current Window ]

...Go to Next Field

End If