Lookup-popup or textlist for product entry on invoice

Panorama X
1

I have been working on improving my business database since moving it to PanX.
In Pan6 my sales database used a choices list populated in .initialize from product in an inventory DB. This worked ok when the inventory had 10 CD titles. With books and other stuff we are way beyond that.

I would like to have it laid out in the form so that clicking on the Title brings up a clickable list with all available titles in the inventory (preferably with no duplicates). After selecting a title the next field (Medium) should automatically check the inventory what media (CD, DVD, etc) are available for this title and only list those.

At the moment I have popup menus for Title and Medium. Entering a number in the Quantity field triggers a procedure which builds the list for the Title popup (using “lookupall” here but this doesn’t omit duplicates). After a title is selected another procedure populates the Medium popup list with available media.

This looks like it’s would be a pretty common requirement and my solution is far from ideal. For example when I have to change the Quantity of a line item, the Title popup for that line gets re-populated and now doesn’t show the Title that was originally already selected (probably should use the Title field for display and separate popup for selection).

So many more questions but this is one of the main ones. I really enjoy Panorama(X) and it’s my fault that I only do programming in it twice a year…t

1 Like
2

I have a similar setup and my approach has been as follows:

In the .Initialize procedure, create an array of your titles:

arraybuild TitleList,cr(),"",Title
TitleList = arraystrip(arraydeduplicate(TitleList,cr()),cr())

In the form, create a PopupMenu Button Object called TitlePopup, set the Mode to Formula and, in the Formula panel, insert the formula:

TitleList

This loads the deduplicated titles into the pop-up display.

In the procedure panel, insert the code:

find Code = TitlePopup

This makes the chosen title the active record. If this is problematic, you could create a Text Display Object and load it up with the lookup( function.

Create a Text Editor Object named for the field that contains the information you want to display say, StockStatus. Then, when you select a title from the popup menu, the title’s stock status is displayed as in:

05 am

I don’t understand what you’re saying here - need more info. Why would Title get repopulated when you change Quantity?

2 Likes
3

Hi Michael

Thanks for your tip. arraybuild works great for my purpose - especially the duplicate removal.
I understand what “find Code = TitlePopup” is supposed to do but don’t quite understand how.

I ended up using text fields for display in the form and a separate little button for a Popup menu.

To answer your question why Title would get repopulated when changing quantity: I had the population code in the code of the quantity field so I didn’t have to close and reopen the database for testing (to run .initialize). Forgot about that.

4

When you select a title, the code snippet in the Formula panel is executed, ensuring that the selected title becomes the active record.

When you choose a title from the popup list, the active record is unchanged. So if Moby Dick is the active record and Dirk Gently is selected, the stock status of Moby Dick will be displayed in the StockStatus Text Editor Object in the form. Finding Dirk Gently makes it the active record and the correct status will be displayed.

5

Whoops! This should read “… the code snippet in the Procedure panel …”

I’ve been thinking about your task and I have some more suggestions: Creating the TitleList array in the .Initialize procedure is not ideal because the popup menu would not reflect any title additions or deletions until you closed and opened the file again so it’s best to create it from scratch every time you need it - Panorama X takes only microseconds to do this.

The other thought that came to mind was that your initial post implies multiple entries for one title - perhaps you have records something like this:

11 pm

If that’s true, you could do the following: In the Formula panel of the TitlePopup PopupMenu Button Object, place this formula:

arraystrip(arraydeduplicate(arraybuild(cr(),"",{Title}),cr()),cr())

Then, the popup menu will be correctly repopulated every time you click on it. In the Procedure panel of this same object put this code;

MediaTypes = arraybuild(cr(),"",{Title +tab()+ ProductType +tab()+ StockStatus})

which creates an array containing the titles, the media in which each is available and the stock status of each media type. It doesn’t matter which records you have selected, the arraybuild( function accesses all records.

Create a second PopupMenu Button Object and name it TitleMedium. In its Formula panel place this formula:

arraystrip(arrayfilter(MediaTypes,cr(),{?(array(import(),1,tab()) = TitlePopup,array(import(),2,tab()),"")}),cr())

This filters out all but the media types in which the selected title is available. In its Procedure panel, place this code:

TitleStockStatus = arraystrip(arrayfilter(MediaTypes,cr(),{?(array(import(),1,tab()) = TitlePopup and array(import(),2,tab()) = TitleMedium,array(import(),3,tab()),"")}),cr())
showvariables TitleStockStatus

This filters out all but the stock status of the selected title and medium and places it in the next object you create, which is a Text Editor Object called TitleStockStatus. You don’t need to enter any formula or procedure for this object. This part of the form would then look like this:

52 pm

You will need to define fileglobal variables for the arrays.

This is all a lot more complex than the original but it does a lot more and it avoids changing the active record. I hope I haven’t made any mistakes in this description but, if any of it doesn’t work or baffles you, get back to me.

1 Like