Checkboxes and checkboxes

[crazybake]

I seems to have done this before - but I cannot get this to work again, and I cannot seem to find the exact answer.

(2) tables - Assets, Students

I have fields Assets::Item a simple text field, and Assets::PermissionOnly - A number field. On a particular layout, PermissionOnly is a checkbox set, attached to a value list with custom values of 1 and 0.

I am trying to create a conditional value list so that Students::PermissionList displays checkboxes of items with a value of 1 from Assets::PermissionOnly. Which can then be checked, when permission for a particular item  is granted.

To make the first attempt, I decided to wait on the checkbox set and use a portal to view data. I tried to create an auto-enter, number field Students::One and set an calculated value to 1, then created an '=' relationship with Assets::PermissionOnly with new table name PermissionAssets. I placed a portal on a Student table layout using the new table PermissionAssets and field PermissionAssets::Item and get no results.

So maybe this is two questions... regardless the end result is the checkbox idea I mentioned.

 

Edited May 29, 2015 by crazybake
left out two words

[eos]

I tried to create an auto-enter, number field Students::One and set an calculated value to 1

​Which will set this field to the value of 1 – in each new record. Have you added a value to the existing records?

You could also simply create an unstored calc field = 1, if this field will never be used on the right-hand side of a relationship.

Edited May 29, 2015 by eos

[crazybake]

Thanks - taking your note above, I started over. I created a new field Assets::One, with an auto calc of '1' and created a self relationship - Assets:One::Assets:PermissionOnly. I was able to create a value list based on this relationship showing only the seond filed value of Assets:Item. Now, when I create a new asset in the table and check the permission only box, it shows in the value list and as a checkbox set of items -  in a field Assets:SpecialtyList within the Assets layout...

Now, the second part.
I have now created the list of items that require permission.

I now need to create a relationship between the Students Table and this Assets table, so that the same checkbox set (or value list) shows in the student table to have the ability to grant permission for specific items which require permission to specific students .

 

[eos]

See if the attached file helps you.

 GrantPermission_eos.fmp12.zip

[crazybake]

Worked like a charm! Thanks for your help

 

[crazybake]

Hmm - since you have helped thus far... can you assist me on this last step?

Within the assets table, I have fields such as Asset:Active, Assets:Locked, and now Assets:Permission. I have a script to check on see if these fields have a value of 1 and to give me the results. This script works fine and will check to see if Assets:Permission has a value of one. However, I will need an additional step to check the value list within the student checkbox set and see if the student has permission for that same item...

thanks

 

[eos]

Hmm - since you have helped thus far... can you assist me on this last step?

Within the assets table, I have fields such as Asset:Active, Assets:Locked, and now Assets:Permission. I have a script to check on see if these fields have a value of 1 and to give me the results. 

​I could be wrong, but I think this hasn't really anything to do with your question?!

This script works fine and will check to see if Assets:Permission has a value of one. However, I will need an additional step to check the value list within the student checkbox set and see if the student has permission for that same item...

​Item = Asset? And what student would that be? And what are doing there (Assets?), anyway?

OK, let's assume you have a selected student to associate them with an asset; if the asset requires permission, you want to check if that student has a permission for that particular item/asset.

SQL

Since you're in a script anyway:

Let ( 
  grantedAssetIDs =
    ExecuteSQL ( "
      SELECT grantedAssetIDs
      FROM Student
      WHERE id_student = ?
    " ; "" ; "" ; IDOfSelectedStudent
  ) ;
  not IsEmpty ( FilterValues ( Asset::id /* current asset */ ; grantedAssetIDs ) )
)

Relationships

The “old-fashioned way”: create a relationship Asset:id = Student_checkPermission::grantedAssetIDs, and use

not IsEmpty ( FilterValues ( IDOfSelectedStudent ; List ( Student_checkPermission::id ) ) )

or in reverse: store IDOfSelectedStudent in a field (a global, since this a pure UI device), rather than, say, a variable; create a relationship Asset:gIDOfSelectedStudent = Student_selectedInAsset::id, and use

not IsEmpty ( FilterValues ( Asset::id ; Student_selectedInAsset::grantedAssetIDs ) )

ExecuteSQL() has the least overhead, but can be bit finicky if you don't know what to look out for.

Filtered portal

What you could also do is use a selector relationship for students and use a filtered portal, meaning you wouldn't need to check your student selection later since you can only select from students with a permission:

not Asset::Permission
// display all students
or
not IsEmpty ( FilterValues ( Asset::id ; Student_selector::grantedAssetIDs ) )
// only students with permission granted for current asset

… but that may be slow, or become so in the long run.

PS: I'd rename “Permission” to “requiresPermission”; that makes the intent clearer, and the calculation more self-documenting.(And you cannot mistake is for “hasPermission”, which would be something else entirely.)

 

[crazybake]

Thanks eos - could not seem to get this to do what I was hoping to do and I was also not very clear in my setup (and may have mislabeled my fields)

I have several tables, Students, Assets, Transactions, TransactionLineItems - it is a system to checkout equipment.

Within Assets I have fields such as ItemNumber, ItemName
and also fields that may limit a student's ability to check something out, such as ItemLock, ItemPermission, ItemPriority, and ItemActive.

When a Student attempts to checkout an item based on the ItemNumber, a script checks to see if one or more of the limitation fields are active. With your help earlier, I was able to get a filtered value list of only those records in the Assets table with ItemPermission checked to show up within the Students layout in a field Students::PermissionList.

What I need to do now is when a student attempts to checkout an item based on an ItemNumber, It needs to check the PermissionList field and see if the ItemNumber exists in this field and, more important has it been checked (or ticked).

[eos]

Sorry, totally forgot about you … 

If you're performing the check from a Student context, you can (more or less) reverse-apply everything I wrote above.

Simply don't lose sight of your goal and the basic (script) steps towards it: in pseudo-code

• there is a selected item; check …
• if that item requires permission; if true, check … 
  • if the item is in the permissionGranted list of the current student record; if not true, then …
    • deny & cancel script
  • end if
• end if
• add item to the Student checkout list (i.e. write a new checkout record)
# if you've arrived here, the item doesn't require permission, or it does but is in the student's access list

Looks like a script, but reads like (is) plain English; next step is to make this a real script.

 

[crazybake]

All seems to work, but before you posted your response, I did a little 'workaround'. I found that if I create another instance of the PermissionRequired checkbox set on the Student layout, but changed it to a text field, only boxes which are checked in the PermissionRequired checkbox display in the PermissionRequired text field. I then created a calculation field with FilterValues on the PermisionRequired textfield, using the Asset::ItemNumber as the filter - and used the if(is Empty) script step - as you suggested. It seems to work.... ?

[eos]

It doesn't make any difference how (or, for that matter, if) you display that field on the layout. Calculations are performed in the data layer; a checkbox is just a UI device that (combined with a value list) facilitates data input and masks/formats the field contents. You could write IDs manually in an edit box version of the field, and it would also do its job.

Try writing a value into the edit box version of the field, and “watch” (so to speak) how it is not visible in the checkbox version (unless it happens to be in the value list). Doesn't change the fact that the value is in the field.

I then created a calculation field with FilterValues on the PermisionRequired textfield, using the Asset::ItemNumber as the filter - and used the if(is Empty) script step - as you suggested.​

There's not really any need to create that calculation field if you need its result only in the script*; rather than use

If [ IsEmpty ( Table::calcField ) ]

do without the field and use

If [ IsEmpty ( calculationThatWasUsedInThatCalcField ) ]

* (with certain caveats about calculation context that don't apply here)

Anyway, I gather you got your check working now? Try this change, then see if it still works …