Perform Find problems

[emballantine]

Ok.

I am working a script that basically moves inventory around in an inventory tracking database. There are Locations things can be and Inventory Items, they are connected by a join table called Items|Locations because item can be in many locations, and each location can have multiple items. 

The layout I am running the script from uses a table called tracking that keeps tracking records every time items are moved. It has a portal working from the table called TrackingLineItems, which is another join table between the Tracking Table and the Invenotry Items table. (See the relationship graph attached). 

The idea is that you put where you are moving items to and from (this data is stored in the Tracking Table), and then in the portal you add the items you want to move.

The script opens a new layout that has all the fields of the Items|Locations table and is meant to subtract the quantity of an item from the location it is coming from and add the quantity to the location it is going to. In other words search for the record in the Items|Locations table that  matches the item and the location and change the quantity (add or subtract).

Seems to all be operating fine except for the Find part. It just keeps posting a "[401] No records match the request" error. Now, I know the record I am specifically trying to access while testing is there. If I open the the Items|Locations layout myself and perform a find with the same location and item it pulls up the record I am looking to alter. In the script it seems to not be able to find the record. 

I am passing the search information via global variables. So, the first part of the loop uses the variable $$LocationComingFrom and $$SerialNumber (of the line item) to then search in the Items|Locations table for the record matching that location and item. 

I can't figure out why? Is it the use of the variables in the find? I am watching the Data Viewer, and the variables are the right value...and if I perform my own manual find with those values it comes back with the right record. Is it the fact that I'm using the script to try to find records in a table that is not accessed in the layout that originally calls the script?

What am I doing wrong in this situation?

Thanks!

STCInventoryRelationships.pdf

[bruceR]

You will need to post the script; or better yet the whole file; if you want help debugging the script.

[emballantine]

I am attaching my file. Please, don't judge too harshly. There are a few extraneous tables that are artifacts from earlier work. And there are some weird issues in the data in a few of the tables because I have been playing around with trouble shooting, etc. Specifically, the item I have been attempting to "move" around the inventory is the Item "Generic XLR3-25". The layout with the script I am working on is called "TrackingFromTo". The script is called "InventoryCheckOut".

STCSVCInventory20150619.fmp12

Edited June 19, 2015 by emballantine

[Kris M]

You should avoid non alpha numerics in table names. I would only use A thru Z, a thru z, 0 thru 9, and underscore. I would also suggest that you spend some time and look at the data definitions and fields involved as joins on the relationships graph.

For example you have Location|Items::Location as text field that functions as a join to Locations::LocationID that is set up as a number field. Their is also a Locations::__pkLocationName that is text but not involved in the join table relationship.

Edited June 19, 2015 by Kris M

[eos]

Your original problem comes at least from your using the quoted form "$$someVariable" in Perform Find[restore], which means that the script searches for this exact string, rather than the value that $$someVariable contains.

But then it is usually better anyway to use the explicit “Enter Find Mode[no pause] – Set Field[](s) – Perform Find[no restore]” pattern. (But if you use the restore option, you don't need to enter Find mode.)

You also shouldn't use $$globalVars unless you really need them.

See here you could further simplify this script:

Freeze Window
Set Variable [ $CurrentWindow; Value:Get ( WindowName ) ]
Set Variable [ $LocationComingFrom; Value:Tracking::ComingFromLocation ]
Set Variable [ $LocationGoingTo; Value:Tracking::GoingToLocation ]
// Set Variable [ $Assignment; Value:Tracking::Assignment ] // not used!?
Set Variable [ $SerialNumber; Value:InventoryItems::__pkInventorySerialNumber ]

Go to Portal Row [ Select; First ]
Loop

  Set Variable [ $LineItem; Value:TrackingLineItems::_fkItemID ]
  Set Variable [ $LineItemQty; Value:TrackingLineItems::Qty ]
  Set Variable [ $oldValue; Value:Location|Items::Qty - $LineItemQty ]
  Set Variable [ $newValue; Value:Location|Items::Qty + $LineItemQty ]
  Set Variable [ $isAsset; Value:InventoryItems::InventoryBinOrAsset = "Yes" ]
  New Window [ Style: Document; Name: "Checkout"; etc. ]
  Go to Layout [ “Checkout|Locations|Items” (Location|Items) ]
  Enter Find Mode [ ]
  Set Field [ Location|Items::Location; $LocationComingFrom ]
  Set Field [ Location|Items::Item; $LineItem ]
  Perform Find [ ]
  Set Field [ Location|Items::Qty; Case ( $isAsset ; 0 ; $oldValue )]
  Enter Find Mode [ ]
  Set Field [ Location|Items::Location; $LocationGoingTo ]
  Set Field [ Location|Items::Item; $LineItem ]
  Perform Find [ ]
  Set Field [ Location|Items::Qty; Case ( $isAsset ; 1; $newValue )]

  If [ $isAsset ]
    Go to Layout [ “Inventory” (InventoryItems) ]
    Enter Find Mode [ ]
    Set Field [ InventoryItems::__pkInventorySerialNumber; $SerialNumber ]
    Set Field [ InventoryItems::__pkItem; $LineItem ]
    Perform Find [ ]
    Set Field [ InventoryItems::_fkAssetCurrentLocation; $LocationGoingTo ]
  End If

  Close Window [ Name: "Checkout"; Current file ]
  Select Window [ Name: $CurrentWindow; Current file ]
  Go to Portal Row [ Select; Next; Exit after last ]

End Loop

Exit Script [ ]

You could probably make this even simpler by going to / writing into related records (rather than opening/closing windows and performing finds), but that's hard to tell without going into the “ins and outs” (pun intended) of your database.

[emballantine]

Thanks, eos. I implemented your suggestions and altered them a bit to make more sense with what I wanted to do. The result is that I have the "non-asset" part of the script working. I have other "asset" related things to take care of before addressing that portion of the script, but you have solved my find problem as well as my looping through the portal rows problem that I hadn't even discovered I had yet (I think).

When I am watching the script through the debugger (pausing on each step) it does loop through the last time with the empty portal row. Ie. If I have two rows populated with information and the next one is ready to take information it will loop through that row to, which seems like not a problem since there is not a quantity to add or subtract. It just runs through calculations with an empty quantity and then exits the loop. Is this normal behavior? Or is there some way to save it going through that last empty portal row?

I am attaching a copy of the script as it stands right now (as PDF) as well as a cleaner (data wise) version of the database as it stands right now, in case you're interested. For all intents and purposes the script does give me the results I need, I am just curious about the empty portal row thing.

Thanks a lot for your help!

STCSVCInventory20150625InventoryCheckOut.pdf

STCSVCInventory20150625.fmp12

[eos]

Good to hear you got it working.

It just runs through calculations with an empty quantity and then exits the loop. Is this normal behavior? Or is there some way to save it going through that last empty portal row?

It is expected behaviour since that “spare” row exists (though it does not represent an existing record), and it is the last one …

Try this: don't use the “exit after last” option, but add 

Exit Loop if [ IsEmpty ( Location|Items::primaryKey ) ]

after Go to Portal Row [ next ].

The absence a primary key can be translated into "this portal row is not a real record”. (You have a primary key in every table, right?)

It would even be better to put that directly after Loop[] – but then you're checking that there are related records (at least one), so if you get to that point, you'll be running the loop at least once. Are you checking …? 

[emballantine]

 

Try this: don't use the “exit after last” option, but add 

Exit Loop if [ IsEmpty ( Location|Items::primaryKey ) ]

after Go to Portal Row [ next ].

I will give this a try. 

The absence a primary key can be translated into "this portal row is not a real record”. (You have a primary key in every table, right?)

Yes. I do have a primary key in each table. 

It would even be better to put that directly after Loop[] – but then you're checking that there are related records (at least one), so if you get to that point, you'll be running the loop at least once. Are you checking …? 

Checking is next on my list. As FileMaker and it's scripting is fairly new to me, I am just working one step at a time. Right now it's working on records I already know exist. Next step is checking for the record and then adding one if it doesn't exist.

I am sure I will have more questions as I dive into that part, as well as I deal more and more with the "assets" portion of the database. This type of inventory tracking (both assets with individual serial numbers and items with (what are essentially) part numbers and a quantity) was a doozie for my first foray into FileMaker. Trying to figure out how to track both types has been (and still is) a learning process.

Thanks for your help!

[eos]

You're welcome.

Once you have this up and running flawlessly, you'll feel very good! Don't hesitate to ask questions – that's what this forum is there for.

[Rick Whitelaw]

eos, I believe Exit After Last when used on portal rows does exit after the last none-empty row. However I could be incorrect because in most cases this would be undetectable.