Jump to content

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


Tohny
 Share

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

Recommended Posts

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!

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.  :smile:

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.  :laugh:

Link to comment
Share on other sites

This topic is 2584 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

  • Similar Content

    • By Richard Carlton
      Performing searches on the web, on the operating system or in your FileMaker file can be slow! But it doesn't have to be that way.  Join Nick Hunter as he breaks down the Search function! Future live streams: https://fmtraining.tv/#LIVE 
      support@rcconsulting.com
      www.fmtraining.tv
      www.fmstartingpoint.com
      www.rcconsulting.com 
    • By quattleb
      I am a foster volunteer for a kitten and cat rescue. Each foster has daily medical and health items we preform for our little wards.
      Years ago I created a simple Foster Tracking database in Filemaker 16 and now use FM 17 on Mac OS Catalina 10.15.x
      Many of these chores repeat at a regular interval so so my Foster Tracking has charts that when given a start date propagates repeating fields with the next occurrence.
      Typically foster arrive in litters of 3, 4, 5 or more kittens and all have a naming theme. I've used "Companions of Doctor Who" and "Gins of the UK" for example. This is stored in a "Name Theme" field and displayed at the top of each kitten/cat record.
      Each time I weigh or give a de-worming or flea medication to a foster group I open the FM db, do a search for the group (e.g. "Gins") in the "Name Theme" field and then I have the medical charts for that group to mark my progress.
      The complication is I often have 3 different litters in three different areas of my home. 
      What I want to do is to have 3 Global Fields - which store the 3 "Name Theme"s I am fostering at a given moment.
      Find_1 "Gins"
      Find_2 "Companions"
      Find_3 "Marx Brothers"
      -----
      Then I want to place these global fields on the main layout that my db opens to. I would define each Field Label to be a Button and have each button search for the appropriate Find_x content in the "Name Theme" field.
      -----
      I cannot figure out how to capture the value of the "Find_x" Global field and insert the value into the "Name Theme" to then Find.
      Nor do I see in the Find function a way to specify that the value comes from the Find_x field but the search itself involve this value in the "Name Theme" field.
      ----
      I feel there must be some simple scriptable way maybe involving an additional layout and a calculation but the how to escapes me.
      I would be very grateful for any help.
      thank you
      Bill qb Quattlebaum
    • By sal88
      Hi all
      I have a customer list in a drop down. The drop down field has an On Save script trigger which then performs a find in the same layout for the selected customer. The drop down field is configured to 'go to next object using' return.
      There is also an On Record Load trigger on the layout which pops up with a dialog if the customer's Attention field is populated.
      This all works fine unless I utilise the option to save the drop down field with the enter button. It ends up looking like this, with the white background. Once the user clicks OK the layout 'appears'.

      Thanks!
       
       
       
    • By Rich
      Greets, and a happy, holiday season to all.
      This is one of those "Well, I could swear it _used_ to work..." FMP (PC) problems: I want a script to create a custom-named folder on the desktop (PC) [for exporting data purposes] so I used the following (see attached) Send Event command: 
      "cmd /c md " & Quote ( Right ( Get ( DesktopPath ) ; Length ( Get ( DesktopPath ) ) -1 ) & "Job Chop-Chop! Backup" )
      ...in earlier versions of FMP--and if memory serves--it used to work. (See https://fmrift.wordpress.com/2010/02/03/creating-a-folder-from-filemaker-windows/ ). However, when I try to run the script in FMP v17.x the script fails with the following error message in the Script Debugger, "[3] Command is unavailable (for example, wrong operating system or mode)".
      So, I'm trying to figure out why the command's failing--could it possibly be it's because I'm using FMP (PC) in VMWare's Fusion PC emulator (v10.1.4, running in OS Mojave) instead of on a "real" PC? Any ideas for a tweak/fix?
      Cheers,
      Rich

    • By Matt Navarre
      Announcing fmSearchResults 5. This latest download delivers new features with simple implementation. And it's free!
      fmSearchResults 5 adds fast, multi-table searching to your FileMaker solutions by importing a few scripts and pasting a simple search field on to your layouts. It feels like the type of Google search that all your users are already familiar with, and it’s far more powerful than FileMaker’s Quickfind feature, because it searches across multiple tables and has data-type awareness.
      Read more about the new features and download it now!
      Implementation is so simple, watch me do it 5 minutes (and 12 seconds).
×
×
  • Create New...

Important Information

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