Value list based on a relationship

[Sverre]

I have a database made in FMP 5.5, "Assets" that tracks inventory. When an item is purchased, its entered into this database with an Asset Number and quantity, and when an item is sold its Asset Number is entered into a different field "Linked Asset Number", and its quantity is entered also.

However, when an item is being sold, I want a drop down list of assets that have not yet been sold. So I created a self-join relationship, between Linked Asset Number and Asset Number, and created a calculated field called "Quantity Remaining" (Quantity-Sum(Assets::Quantity)), which correctly calculates the number of units remaining for each asset. Then I added a calculated field, "Available Asset Number" (IIf(Quantity Remaining>0,Asset Number,"")).

My problem is that I can't seem to use "Available Asset Number" as a value list for my drop down list because its a calculated field based on a relationship and therefore can't be indexed!

If in my value list I say "include second field", and use description, and sort by that, the value list works, but it includes ALL assets, available or not, and if I sort by the first field I get the error "index not available".

How can I do this?

[ibookdoug]

Hi,

You need to use the valuelistitems function to get your value list.

The one problem you may find is that it will quite slow.

Hope that helps

[Robert Kidd]

You could have a value list displaying all asset numbers with the second field displaying either a status ie. stock / out of stock, or the current stock level.

OR

Every time a sale is made the related stock level (number field)could be updated with a script. Use this field to determine the stock status. You would need to be careful with record record locking issues if two people sold the same stock item at the same time.

[Vaughan]

I have done something like this. It's convoluted and requires some serious string handling.

I made a global field to hold a list of "available" record IDs. Another global to hold "entered" record IDs. You'll need two self-join relationships to display the "available" and "entered" IDs as scrolling portals on the interface's layout.

When the user enters the interface the list of available record IDs is pasted into the "available" global field, then all existing "entered" record ids are subtracted from it. This will give you a list of available option on one side, and entered option on the other.

When the user clicks on one of the available options (one of the available portal's rows) a script adds that record ID to the "entered" global field, then subtracts the record id from the "available" list, then refreshes the screen. When the user is done they run a script that finds the "entered" records and processes them accordingly.

Note carefully that this technique bumps up against FMP's 64kB limit for text fields, so there is a physical limit to the number of options that can be listed in each portal. That number is 64K divided by the length of the record id string plus a carriage return. If the record id plus return is 64 bytes (pretty generous for a simple serial number) then you'll max out at about 1000 records. For many purposes this could be adequate. It was for me.

[Sverre]

I was really hoping to do it without scripting, because the user has to click a button when they have finished entering the data, they can't just hit command-q, do a search, change views etc etc.

There might be something useful in the valuelistitems function, so I'll look into that.

Its true that I could also list ALL assets, available or not, but over time the value list will become REALLY long and a bit user unfriendly.