Multiple records in Find Mode

[Megee]

i want to make a delivery list by entering in an ID# but when in find mode i type in an ID# it takes me into browse mode. can i make a list by ID#?

[-Queue-]

You need a new find request for each 'or' criteria. If you are scripting it,

Enter Find Mode [Pause]

Loop

Show Custom Dialog ["Do you want to enter another request?"]

Exit Loop If [not Get( LastMessageChoice ) = 1]

New Record/Request

Pause/Resume Script

End Loop

Perform Find [ ]

[ddreese]

Is the ID# NOT unique? As in if it were an invoice and you wanted to find all the records that were purchased by one company using where the ID# represented the company?

If that's what you're asking, the easiest way to do it would be create a new layout that is list view instead of form view, enter find mode on that (new)layout, enter the id number, and when it searches it will list all the records that have that ID# you are looking for.

To clean it up a bit, I'd create a button and assign it a script that does:

- Show custom dialog (allow input for a field, create a new global field called 'global_ID#' or something)

- Goto layout (One that is list mode)

- Set ID# to the value of global_ID# (i.e. whatever you entered in the dialog box, make sure ID# is a field on the layout)

- Perform Find

That's about it.

I'm sorry if that's not what you're asking at all

[-Queue-]

If ddreese's assumption is correct, then it would be more efficient to relate global_ID to ID and use Go to Related Record [show only related, global_ID_to_ID].

[ddreese]

Yea sure if you like doing it the fancy-way!

[MoonShadow]

Not only is Queue's suggestion "the fancy way," but it is also much more User-friendly. I think Magee might want to enter multiple entries at once (but I could be wrong) ...

Staff enters a list of Invoice Numbers (or Customers or whatever) in the global. That's how we quickly find invoices when checks come in. It can even be a self-join ... from this global text to its own ID. They enter it as a list, separated by carriage returns. number [enter], number [enter] ... very fast and easy for them to enter ... and very fast to retrieve. And this multiline becomes the left key. I don't like having to enter repeated Find Requests for each item; nor do I like having to enter each item in a table row - takes too much time.

Quick-type everything you want to match, GTRR to that Table Occurance (which is just a way of presenting the data) and it's done. Fancy? Yep, in any FM version. A self-join and GTRR is almost always instant, regardless of the record volume ... compared to searching through a 180,000 lineitem table when you are only looking for a subset? Truthfully, I only like to use finds if they are 1) either very quick and easy (for me), 2) are more rarely used and/or produce small numbers of records or 3) they are so complex that they would require Aggregate functions (for defining the keys) which are dawg-slow. I prefer instant.

[Megee]

ok, so im pretty sure thats close to but not quite the solution im looking for. thanx so much for all of your suggestions. let me try to explain exactly how i will be using FM Pro. i print packs for the WMU bookstore. each pack for each semester has a UNIQUE ID#. when the printing is finished a delivery list needs to be made. lets say pack # 104, 110, and 112 are being delivered. i want to type in the ID# and have the Quantity and Name of the Pack be filled for me....id also like to be able to change a field if we have to deliver 1 or 2 extra.....please forgive me and my lack of experience but my boss handed me the FM program and said have fun playing.

[MoonShadow]

Magee asked ... i want to type in the ID# and have the Quantity and Name of the Pack be filled for me...

Where would the Quantity and Name of the Pack come from? A related table or something you enter? If related, you can use a Lookup.

If this is just a single table, you can set both fields using a looping script added after your Find and before your layout switch. Let's narrow down more specifically what you need (by you providing more information about your existing table(s) and then we can provide a script or other solution to get you there.

No problem with being inexperienced. We all are ... always. But I just don't know what to give you because I still don't know what you have. If in doubt, go to Define > Database and look at your Relationships tab for any pertinent tables. And we need to know if you will be setting regular fields in records that already exist or whether you will need to create new records. How is the ID# associated to the Quantity and the Name of the Pack?

[Megee]

im trying to work with one table with multiple fields. it has one main layout where i enter in the coursepack ID, pack name, class name, professor name, address, and many other options for printing and binding (about 25 total fields). then i made a layout to create a delivery list and on that layout i have placed fields from the main layout such as coursepack ID#, quantity(printed), professor name, and class name. this is where i want to go to find mode and type in different coursepack ID#s and have the other fields filled in as they do normally. but with copyright delays and production problems there are always different numbers of packs going to the bookstore. so here is where i would like to be able to type in different pack numbers and possibly change the quantity field. any questions? thanx again

[MoonShadow]

Hi Megee,

I have attached a simple demo which shows what I think you want. There are only three pack records - 100, 101 and 102.

If you type 100 then [return] then 102 then click the button, it will *find* two of the three packs. Normally, you would just keep typing your list in the yellow global field. I use this to enter 25-50 invoices at a time and I think it's easier than multiple find requests.

You can then modify the quantities on the list and then print it. I've included a Delivery Date script also which shows how to then set all your packs as delivered. I hope this is helpful.

Packs.zip

[Megee]

that sample is great. exactly what i want.....now i gotta figure out how to make mine do that! im still confused on the scripts and how they relate to your fields and layouts.....ill play with it and post later...thanx a ton

[Megee]

ok, im getting where i want to be but once i created my "back to main form" button, the "create delivery" button stopped going to the next page....im still playing and i found how you put the cursor on 'quantity' on the 'deliveries' layout...when i try to set that in my script, the 'deliveries' layout isnt an option...in your define db you have the relationships where it looks like you have two tables...im not finding how to create that relationship between layouts. plus i need lunch......hope to hear from you soon

[Megee]

ok, i thought i posted earlier....guess not....perfect demo...ive played with it and im closing in on what i need. i had the delivery list semi-working (the list wouldnt clear when i tried to make another list) and i was trying to make the cursor go to the quantity on the delivery layout....i found how you did it with relationship betweeen layouts but i did not see how you created the relationships....something about occurences in graph. i can make the cursor go to quantity on the main layout because that is the only option. hope to hear from you soon

[Megee]

why did my old posts just now show up?!?!?!?!!? whatever.....hope you get my point through all those different postings!

[MoonShadow]

Well you're having a time of it Megee.

Create another Table Occurences of Packs. Point at the gPackID field in the original table and drag and point at the PackID in the newly created copy then release. This establishes your relationship - and is what makes the Go To Related portion work correctly.

If you duplicate everything as I have, your results will be the same. Don't forget the Commit Records part (in the Deliveries script) or your button won't activate. Let me know how it goes.

[Megee]

a quick one for you...if you will=) i wont normally want this, but how do i reset my incrementing numbers (pack id and delivery #)...if i delete a record or all of them i would like the numbers to start over

[MoonShadow]

IDs should be meaningless. If you delete a record, there is no need to recapture (and use) the serial assigned to it. And if you change the PackID, you will orphan any related records. This should be used with great caution. However, you can accomplish what you wish (if you delete all Pack records) in two ways:

Manually: Delete your records then go to your PackID field and change the Auto-Enter serial number.

Via Script: To re-set your PackID to 0001 after deleting all pack records your script would look like:

Show All Records

Delete All Records

Set Next Serial Value [ PackID; "0001" ]

If you only delete one PackID, you won't want to reset, because the record may not be the last one.

[Megee]

hey moonshadow.....im lovin all your help.....im going insane tho. can you remind me how to have a field involved in a calculation be set as a default decimal (.11) and also be editable as you did the quantity for me?

[Megee]

hey, i found how to do the decimal thingy. but the question i had before..... while in the trial process we have created multiple new records (about 10) so my ID# is up to 111. i want to start the numbers over just one time....we are now trying to enter in real packs (instead of trial packs) and i want the numbers to start over at 101 because when we create a new record the id increments to 112. should i use that script? if so im not sure how to use a script outside of a button (if possible), would i create a button to run the script then delete both button and script?

[MoonShadow]

You can set a numbers default decimal by right-clicking on the field, selecting NUMBER and specifying a fixed format decimal place. However, you mentioned calculation ...

I am unsure what you are working on, Megee, so can't really answer. Are you working on pricing for the Packs? If you have a number field that you are using within a calculation (type text), the answer would be different. Can you provide specifics such as the field types involved and what the calculation is - text, number? If simply changing the field format as suggested above is not what you need then I need a bit more information, okay?

[MoonShadow]

We just cross-posted!

No problem resetting your Pack IDs then. And you don't need a script at all. Just delete all records to start again. Manually open Define > Database and go to your fields tab and the PackID field. Open Options and Auto-Enter and change the 'serial' number to 001 (or whatever you want to start with.)

[Megee]

in my db i have to choose what department the pack is for (acty, cis, hist, span). i also need to specify the department address to where the materials should be delivered. i was wondering how or if i could associate the address fields with the drop down menu i created for each major/department WMU has. what i need is when i select accounting, the office number to fill in the number field and the building name to fill in as well. sound possible?

[MoonShadow]

Megee asked ...could associate the address fields with the drop down menu i created for each major/department WMU has. what i need is when i select accounting, the office number to fill in the number field and the building name to fill in as well.

Is each major/department WMU has unique? Is there only one office number and building name associated to each department? If so, this sounds like a perfect situation to use a lookup. You would create another table occurence of your table (maybe call this one Departments. Drag/join this department field to itself. Go to your office number field in your main table and specify Auto-Enter, Lookup and select your new Departments table occurence and select your office number field from that TO. Then go to Building Name field and specify it as lookup also based upon your Departments TO. When you enter the Department, the office number and Billing Name will fill in.

If you have more than one office number and/or building name which can apply to one Department, you will need a conditional value list instead - which will filter your selections, ie, only associated office numbers and building names to one department will appear in the popups, but a User will still have to select WHICH office number to use. Let me know if this is what you need instead. I think lookups will work for you.

In (relational) theory, you are getting into an area in which your Departments should be a separate table. They would contain DepartmentID, Name, OfficeNumber and BuildingName. I am unsure whether to recommend you change your structure. Keep in mind that having your entire solution in one table is possible but if it continues to grow in complexity, you might wish you had considered a different structure.

[Megee]

lookups should work......right now my dept and buildings are in drop down menu form. i dont see how to associate each building to the departments that are inside them, along with the address for each dept. just by relating them wont do what i need, will it?

also, you created a delivery list for me in that sample file. im trying to get the delivery list to clear once i click on the 'back' button. in the script for 'back' would it be 'clear all records' or something like that?

[MoonShadow]

The delivery demo I provided sets the delivery date on the Packs then clears the global PackID field when you click the back button. You wanted to manually select which packs to deliver. What do you mean by 'Clear All Records'? Surely you don't want to delete them? Don't you want to leave the Packs processed for a history of work done?

Did you try my suggestions of lookup or conditional value list? I can't solve your problems without knowing what you have tried, and then knowing what didn't work and why. It is difficult to see your solution through your words and it would be helpful if you attached your file.

[Megee]

alright....i tried the lookup stuff and it worked with one record...truthfully some coworkers are using the program and i have only heard of it working once and they also may not have entered in any more packs. ill try to attach the file so you can see how im doing. what im trying to do is clear the 'delivery to wmu bookstore' list once i click 'back' so the next time i create a new list it only has the packs for the new delivery.

[MoonShadow]

A few things I spot off the bat ...

Your delivery list script points to your original WMU relationship instead of your new WMU2 delivery relationship (Go To Related). When you change your script, be sure to specify the correct layout here also. And change your Layout itself (Status List?) to point to WMU2; otherwise, if WMU is specified as the layout (as you currently have it), it won't work.

You have your ClassID set as a Lookup. But the ClassID is used in the relationship from ClassID to ClassID for your Departments TO. That needs to be selected by staff. Once they enter the ClassID, your Building and No should look up properly.

But, as I said prior, a lookup will only look at the first matching related record. I asked if you had multiple same Class records (although it was called something else before) with different buildings and numbers - if so, you needed a Conditional Value List instead of a lookup which will filter the Numbers according to the Building.

A lookup will grab the first matching record but your data is skewed. How? Example: Your first HIST record has the Building and No blank. Your next HIST record has Friedmann Hall No 4408 and the next HIST says Friedmann Hall No. 4020. How can a lookup work on that?

Enter a Building and No in that very first HIST record, remove the lookup from your ClassID, then create a new record. Type HIST in ClassID. Building and Number will fill in according to the first matching 'related' record. The lookup works but it still isn't what you really need; because, how can you determine which No should be used if there is more than one number per building?

I suggest you work on correcting these issues. Once you've corrected them, post a new question with only your remaining problems. This thread is getting too long and muddied to be useful. Okay? And I will continue my assistance as I'm able on the new 'improved' program on a new clean thread - which (hopefully) will only have one or two issues at a time to fix. And I suggest you search Forum for Conditional Value Lists and Lookups to better understand how they work.

[Megee]

i was afraid you would say that....im running into so many questions that i dont know what to ask.

[MoonShadow]

Hang in there.

Fix the things we've discussed and you'll be in pretty good shape. Then we can continue with just the few remaining problems you're having - in a new thread.

[Megee]

off the subject of my project. can i delete the post i made with the attachment so no other people download it?

[MoonShadow]

You can't delete your post because someone has responded after you. But you can edit your post and when you reach preview, you can click a box which says to delete the attachment.