pc Use search box to find record and change another field within that record.

[Tohny]

Hello Everyone,

 

I'm having a hard time working out how to do a search and replace.

 

How I have my FM project set up is; I have three tables. 

 

The first is Staff, it has these fields: id, staffID, firstName, lastName, fullName (caculated).

The second is Assets, it has: id, staffIDFK, dn, make, model, etc...

The last is Resources, it has: id, staffIDFK, assetsIDFK, addItem.

 

What I have is on the Staff page I have two tabs, the first tab shows all of the staff members information, the second tab has a portal showing all assets assigned to them. Above the portal I have a field(addItem) from the Resources table and a button.

 

What I'm trying to do is have the ability to type in the DN into the "addItem" field and press the "Add" button to add the device to the current Staff member. DN stands for Device Number specific for each item, its like the id for the item but I was told i should always have an id field in each table.

 

How I was hoping the logic would go something like this:

 

If (content from Resources::addItem is found in any Assets::DN fields )

    Check Asset to see if Assets::staffIDFK is empty

    If NOT, Message box: "Currently Checked out to "Staff::fullName" do you wish to add anyways?" Yes/No

    If YES, Set Assets::staffIDFK to Current Staff::id

Else if (Resources::addItem is not found)

     Message Box: "No Item Found."

 

 

I really hope this makes sense. Thank you for your help in advance!

[Tohny]

For others out there like me I solved my own problem with the guess and check method

 

First on my search field it is located in another table, so the first thing I had to do was do an OnObjectExit I set a global variable $$dnID.

 

For the ADD button that was created, I used the following script:

Set Error Capture [On]
Set Variable [$$staffID; Value:Staff::ID]
Go to Layout ["Asset"(Asset)]
#
Perform Quick Find [$$dnID]
#
If [$$dnID = Asset::DN and IsEmpty (Asset::StaffIDFK)]
  Set Field [Asset::StaffIDFK; $$staffID]
  Commit Records/Requests[]
#
Else If [$$dnID = Asset::DN and not IsEmpty(Asset::StaffIDFK)]
  Show Custom Dialog ["Warning"; "This item is currently assigned to " & Staff::FullName & ". Do you want to continue?"]
  Set Field [Asset::StaffIDFK; $$staffID]
  Commit Records/Requests []
#
Else
  Go to Layout ["Staff" (Staff)]
  Go to Object[Object Name: "ItemList"]
  Show Custom Dialog ["No Item Found"; "There were no items found matching that name."]
#
End If
#
Set Variable [$$dnID;Value:""]
Show All Records
Go to Layout ["Staff"(Staff)]
Go to Object [Object Name: "ItemList"]
Set Field [Resource::AssetAdd;""]
Commit Records/Requests []

I hope this helps someone else out there!

[eos]

1. Is the purpose of the Resources table to denote Assets that are (temporarily or permanently) assigned to Staff members?

 

2. Don't use global $$vars unless necessary; a local $var would do just fine here – though I'm suspecting that your script could be very much simplified anyway, therefore my above question

[Tohny]

Hello,

 

All Assets are "Temporarily" assigned to staff members, when on leaves the asset becomes available.  As for the Resources table, I created it to hold misc. fields that do not belong in other tables. Like the Asset Search box, I don't think it should go into the Assets table when its not holding any data related to the Assets other than storing a temporary value.

 

Now I'm VERY new to Filemaker so I could be quite wrong on my assumption.

 

For Global Variables my understanding on them was that a table could not see another tables variables unless it was a global. Again I could be wrong.

 

 

Thanks for your help!

[eos]

All Assets are "Temporarily" assigned to staff members, when on leaves the asset becomes available.  As for the Resources table, I created it to hold misc. fields that do not belong in other tables. Like the Asset Search box, I don't think it should go into the Assets table when its not holding any data related to the Assets other than storing a temporary value.

 

This then is a bit confusing (to someone not familiar with your database), since “Assets” are a part of your business logic, while “Resources” are tools internal to the database and not actually pertinent to the discussion at hand.

 

For UI tools such as search boxes, you normally use a global field anyway; maybe create a “Globals” table where the name makes clear that it is related to development, and does not hold actual data. “Resources” is a bit too ambiguous for my taste (but of course YMMV).

 

This also explains why you have a staff ID in the Assets table, where normally it shouldn't have any business being; and it means that you overwrite the staffID each time the asset is assigned to someone else. Note that this means you have no history of which asset was assigned to whom in the past.

 

As to variables and fields: don't confuse them with each other.

 

Fields need to have a global storage type to be visible across all records of a table (and consequently, the entire file).

 

Variables are usually more concerned with their scope (visibility) within scripts. If you have a variable that you only use within a single script, it can be local; global variables are used if you need to access their values either in sub-scripts (though there are other means to pass values), or want to utilise them as temporary containers outside of any script. Their scope is the entire file in which they were created.

 

Now I'm VERY new to Filemaker

 

For someone SO new your script is pretty decent. 

[Tohny]

Thank you for the heads up on naming, I think I will go back in and change the "Resources" table to "Globals". My line of thinking was, it's being used to perform other functions therefor becoming a Resource, but I can see how that would be confusing later on if I do indeed have "Resources" I need to create.

 

I think I understand the Variables, the reason I used the global variable $$dnID was because it was pulling the information from the "Resource" table and using that variables data in a different script.

 

Now you brought up a good point I didn't really think about; as you said the way the script works is it finds and replaces the Staff ID inside the Assets table "linking" that asset to the staff member. What would be a better way to do something like that? You mentioned the possibility of adding a history option which would be nice.

 

I tried going from the Assets starter template in FM13 but it didn't have the kind of functionality I needed to assign items. So I ended up starting from a blank template and building from there what I needed. Now that I think about it, I believe it also had a "history" drop down.

 

Would the history section work like a buffer between the "Staff" and "Assets" tables? Where the "Staff" Form would be displaying the results from the "History" table rather than the "Assets" table?

 

 

Thank you for all your help!

[eos]

Now you brought up a good point I didn't really think about; as you said the way the script works is it finds and replaces the Staff ID inside the Assets table "linking" that asset to the staff member. What would be a better way to do something like that? You mentioned the possibility of adding a history option which would be nice.

 

I tried going from the Assets starter template in FM13 but it didn't have the kind of functionality I needed to assign items. So I ended up starting from a blank template and building from there what I needed. Now that I think about it, I believe it also had a "history" drop down.

 

Would the history section work like a buffer between the "Staff" and "Assets" tables? Where the "Staff" Form would be displaying the results from the "History" table rather than the "Assets" table?

 

Actually, it's not really a History table, that's a side benefit; it's more like a Past & Present table.

 

But you're correct in that this table is placed between Staff and Assets; in a portal, you could actually display from both related tables (see diagram further down).  

 

To be more precise, this is a join table, which usually is not about holding primary data, but recording combinations of primary data and/or processes they're involved in.

 

Consider a structure like

 

Staff --< Assignments >-- Assets

 

where --< signifies one-to-many, and the Assignments table has fields like _fk_assetID _fk_staffID,, dateStart and dateEnd (and its own primary key). This also means you can get rid of the StaffID in the Assets table.

 

Every record there denotes the fact of one item being assigned to one staff member for the duration from dateStart to dateEnd.

 

´The Assignments table now holds all data you need to see:

 

• which Assets are currently assigned

• which Assets have ever been assigned

• and (for both) to which Staff members, and when

• visible in the table itself, and from either “parent”

• which Assets have been used during a given date range

• etc.

 

Well, simply search this forum (or Google) for “join table”, and read all about it. 

[Tohny]

Sweet, Thank you for all your help! I'll go research Join Tables now!