Jump to content

Value list based on a relationship


Sverre
 Share

This topic is 6572 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

This topic is 6572 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.