Jump to content

  •  

Photo

Keep certain certain fields grayed out unless condition is met

conditional field layout

  • Please log in to reply
5 replies to this topic

#1 Tron50  member

Tron50
  • Members
  • 7 posts
  • FM Application:13
  • Platform:Cross Platform
  • Skill Level:Novice
  • Time Online: 6h 34m 36s

Posted 16 January 2014 - 08:32 AM

I'm new to Filemaker so excuse my naiveness (although I have programmed in other languages).  I have 2 tables that are related in Filemaker and essentially on the form I only want fields from the second table to show on screen or be unhighlighted if the record in the first table is found.

 

For example, perform search for item_no in 1st database and if found display item_no and vendor, once that is found open a new record in the second table for user to add info for the item found.  Also if the item is no located in the first table, give user an opportunity to add a new one and then go on to open a blank record in second related table.

 

Any easy way for me to implement this is appreciated.

 

Thanks

 


  • 0

#2 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,886 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 238d 57m 2s

Posted 16 January 2014 - 09:39 AM

Hi Tron50, welcome to FMForums!!

 

It would help to understand the purpose of those tables and their names and what functionality you are trying to create.  

 

But with 13, you can easily hide any object or field ( see Inspector > Data tab > Hide ) so all we need to do is find out what you are doing to determine what criteria should hide the fields.

 

But without context and purpose, there may be better approaches to this issue than simply hiding fields (which DO take resources) so I would suggest supplying a bit more information so we can provide relevant and useful suggestions which more closely fit your needs.  It would also put my mind at ease if you clarified how you have the tables related since it is the first mistake those newer to FM tend to make.  

 

Again, welcome.   :laugh2:


  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#3 Tron50  member

Tron50
  • Members
  • 7 posts
  • FM Application:13
  • Platform:Cross Platform
  • Skill Level:Novice
  • Time Online: 6h 34m 36s

Posted 16 January 2014 - 09:55 AM

It is a database to manage inventory in a warehouse.  The first table manages the item no and the description of the item, while the second table contain items and their location in the warehouse (i.e. when a pallet comes in they have to be stocked in a location)

 

Table 1 Fields          Table 2 Fields   

IDNO                        IDNO                

Item_no--------------->item_no

Item_Desc                Section

                                 Location

                                 Lot No

                                 Floor

 

The purpose is this, we do not want them to enter an item no that already exists but rather perform a lookup in table 1, if there item is found then allow them to add entries to table 2 for those items.  On the other hand if item is not found in table 1, provide an  option where they are prompted to create a new record for that item first before it proceeds to Table 2.  Perhaps there is a better way of doing this, also we would like the user who is connecting the database to have their name added to a field so it can be tracked who created the record.

 

If there is a more elegant want my ears are open.

 

I hope this helps

 

Thanks

Paul


  • 0

#4 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,886 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 238d 57m 2s

Posted 16 January 2014 - 05:22 PM

Hi Paul,

 

There are several approaches but the attached shows easy way whether your User is working in Items and wants to add multiple quantities to several Locations or whether in the item's Locations table wanting to locate its Parent ( or create new parent) while adding entries there.  I suggest meaningless keys because even though unique, I have seen manufacturers change their minds and cross-references abound.  If you keep YOUR relationships using meaningless keys, you will always be * safe.

 

I have turned on Allow Creation both directions in the graph between their standard relationship using proper keys. The Item No is selected from a popup value list (see File > Manage > Value Lists ) which creates the relationship.  From there, the related 'parent' Item data is displayed in lower right of your Locations layout.  

 

If the User does not see an Item in the ItemID popup when adding a record (you can rename that label), they simply go to the parent fields lower right and type in the Item Number and Description and, because Allow Creation is on to the parent table, a new Item record will be created.  Any Item fields you wish can be 'cross-placed' in this way.  

 

ALL fields which might have field-level validations from the Items table must be present on the layout.  If not, and an Item field fails validation, the User cannot fix it and (if your validation is strict) will get STUCK so include those fields on the layout; same is true if reversed and adding into portals ... if a field requires validation and fails, User must have access to that field in the portal or they cannot correct it. 

 

Proper validations have been added to the Item_No in your Items table as example.  You will see it in action when you try to add a new Item below right into Items and it already exists.   Type aa into the empty Item number below right and see the validation in action since aa Gizmo already exists.

 

CreatedBy field has been inserted into both tables where your User can be tracked on who created records (see field definitions, Options > Auto-Enter tab and upper section).  If you go now to the Items layout you will see you can add multiple entries that way - just type into the portal.

 

There are other approaches but this is the simplest if it will work for you.  If not let us know and we can look at other options or others might prefer other techniques.  For instance, if your Items table holds 25 fields to fill in and you have complex triggers on its data-entry requirements, you might not want to just type into Item fields on the ItemLocations layout.  But regardless, this file is standard FM relational stuff showing keys and perspective so it will give you a good start.

 

Added - * another reason for meaningless keys is that someone can mistype and you need the ability to correct their typing the Item Number incorrectly without breaking your relationships.   :idot:

Attached Files


Edited by LaRetta, 16 January 2014 - 05:36 PM.

  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#5 Tron50  member

Tron50
  • Members
  • 7 posts
  • FM Application:13
  • Platform:Cross Platform
  • Skill Level:Novice
  • Time Online: 6h 34m 36s

Posted 23 January 2014 - 03:34 PM

Hi Laretta,

 

This was really helpful as it is starting to give me an understanding of Filemaker (came from a Dbase, Clipper and C background)  I have attached a zip file with the databases.  Really the customer only needs two itdesc and itloca as itloca  can contain all the individual transactions with the additional created by and datestamp of each transaction which is necessary for creating reports of who did what and when.

 

My questions are as follows
 

1. In your items.fmp, I like the idea, but the itemID popup does not appear to allow a user to begin typing in an item and have the list scroll to the nearest find, if the database is thousands of records long which it can be (perfume company) this can take some time to scroll, of course again maybe I'm not seeing it in the right way or even using it correctly.  Your advice is appreciated

 

2. Is it possible to have the items and the item location on the same screen.  This way they can validate it and at the same time begin entering transactions for the item.

 

3. Where is the cross creation for related tables feature 

 

I really appreciate your help, it has sped up my thinking 

Attached Files


  • 0

#6 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,886 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 238d 57m 2s

Posted 23 January 2014 - 11:42 PM

You have Item_No as the join and it needs to be IDs. Also, no value list was attached to the itDescID field. I have modified your file and assigned the proper ids through all three tables (including the old History table) and I'll explain step by step since this needs to be done in sequence. I have also returned your file (LocatorMOD.fmp12) with these same changes and you can refer back to my original file showing the same things I will walk through now. These data modifications should be ran when Users are not in the system.

 

Establish new Ids in the parent table (idDesc) – back up first of course

 

Place itDescID field on your itDesc layout. Show all records (it will automatically unsort) and place your cursor in itDescID. Now Record > Replace Field Contents [ by serial, start at 1, increment by 1 and check 'update auto-enter options] then select 'Replace' button.

 

Pass these new itDescIDs from parent to its child tables (itLoca and itHist) – back up again first.

 

The idea is to use the old keys ( the existing highway where joined on Item_NO ) to pass through the newly created itDescID and that is easy to do because we use perspective. From a 'many' table, its parent is a single record. So standing in a child record, we can directly ask anything of the parent and get the right result so we just ask for the new ItDesc::itDescID.

 

Add new itDescID field to itLoca, result is number. Go to a Add New Item (itLoca table). Show all records, put cursor in the ItLoca::ItDescID field. Now we will do same as above except instead of replacing with serial, we will replace with a calculated result of the current records' parent idDescID. So again select Records > Replace Field Contents [ ItDesc::itDescID ], then the Replace button.

 

Next connect itHist to itDesc in your graph based upon Item_NO. Add itHist::itDescID to itHist tabl … Go to layout based upon ItHist … go through exactly like itLoca, show all records, place your cursor in itDescID and replace with ItDesc::itDescID.

 

Now go to your graph and double-click the = between ItDesc and ItLoca and change the keys from being based upon Item_NO to itDescID on both sides. Below check 'allow creation' on both sides (which answers your question #3). You can now remove Item_No from both ItLoca and itHist as they are redundant.

 

Now for your questions:

 

  1. … the itemID popup does not appear to allow a user to begin typing in an item

If you have a smaller number of Items, it is good to use a pop-up menu since it deters from mistyping but you can also try out a drop-down list … type the first number of your Item_No. Try it out by typing apl7 quickly in LocatorMOD and watch it jump. Since I used a drop-down, it does not display the 'second field' value after leaving the field so I stacked a merge field of ItDesc::Item_No on top of the id which hides the underlying ItDescID field.

 

However, with over 20-30 items, even drop-down is lacking and usually a portal placed on popover or in small picker window is used instead. When the user selects an item in the portal, the itDescID is inserted into itLoca::itDescID. Picker portals can be further filtered by first character typed … there are many posts on the subject with various techniques. Search for 'picker' or 'portal filter' In your file, you had no value list attached to the itDescID field (see either of my files for the value list). I added a drop-down list and attached it to your itLoca::itDescID field.

 

  1. Is it possible to have the items and the item location on the same screen.  This way they can validate it and at the same time begin entering transactions for the item.

     

Currently when you select an itDescID any fields placed from itDesc table onto Add New Item (itLoca table) will fill in. You didn't have parent fields on your layout … remember those lower-right fields in my sample were from Items table and they show how you can simply place any parent (Items) fields onto child tables and they just display … no need to add them again to the child.

 

You appear not to have a location as an entity but rather a series of fields: Section, Row, Bin, Level and Multi. I am unsure how you wish to validate locations. On Rows, you can use a value list to display 'values from field and all values' for Row numbers but I suspect that is not what you need. I suggest you set aside the location validations since it can be added as a separate piece (and possibly another table) and should probably be fleshed out in its own thread (if I understand correctly; it is like SLOTS). You can search for SLOTS to find a good post by Comment on it.

 

Every table which holds data should have the critical 5 fields (unique meaningless ID, creation & modification timestamps, createdBy & modifiedBy or their equivalent staffID). You need them because, aside from telling you who created a record, you cannot retrieve, investigate or repair data in tables effectively without these five fields.

 

Feel free to provide more information or ask further questions.  

 

added blue.  File didn't attach the first time.  Here it is.  :-)

 

LaRetta

Attached Files


  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.




FMForum Advertisers