Jump to content

Multi-criteria relationship. Calc field involved.

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

Recommended Posts

Using a multi-criteria relationship I want to accomplish a conditional value list.


pk_ID (not equal) fk_ID

cTotalAmount (greater) gZero


but as cTotalAmount is a calculation field it doesn’t work properly.


I’ve tried with a calculated numeric field (=cTotalAmount), but this doesn’t updates automatically as a calculated field.


Any suggestion?

Link to post
Share on other sites

pk_ID (not equal) fk_ID

cTotalAmount (greater) gZero


Your explanation is not clear, esp. the context. Please name the tables involved and explain [a] which table is supposed to be the source for the value list, and how exactly are the matchfields defined - for example, is gZero a global field?




How often does the calculation change?

Could you run a scheduled script to populate the field every hour / whenever?


That's not a good idea: you want your data to be correct at all times - not just on the hour or "whenever".

  • Like 1
Link to post
Share on other sites



That's not a good idea: you want your data to be correct at all times - not just on the hour or "whenever".

That makes sense.

Would it still be a bad idea if the script was triggered whenever the relative record or field was updated?


It will be interesting to see how this relationship is going to be used!

Link to post
Share on other sites

Would it still be a bad idea if the script was triggered whenever the relative record or field was updated?


It depends on whom you ask. I would rather not depend on a script trigger (i.e. a layout-level device), if the data can be made to self-organize using calculations and relationships (i.e. data-level devices). But Wim's answer may be different ...


And, after all, a conditional value list is a layout device itself - so this may work even if the field is updated by the action of the user wishing to use the value list, just before doing so. However, you must also consider the possibility of record locking by other users.

  • Like 1
Link to post
Share on other sites

As my ability to explain myself (only in English :yep:) is quite bad, using the some of the Estimates starter solution structure, I’ve made a sample file to try to throw some light on it.


The example goes about people and their bets.


My goal is to make a value list that shows only Bettors with balance enough to bet, then, only the ones that are not already betting.


But writing this down I realized that there’s another problem.


At first, Bettors can have a positive balance (so the VL will show them), but the amount they have to face, depends on the number of bettors, so that positive balance could not be enough to cover the bet :shocked: , and this could become a vicious circle :logik: .


Thanks for all your answers.


Link to post
Share on other sites

This topic is 2021 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
  • Similar Content

    • By Tony Diaz
      The tables.
      The Genre data is already arranged like this:
      Genre::ID (Unique Key)

      The Platform data is 'simple'. ID and Name. But each item could be multiple platforms.  (Example #1 vs. Example #2)
      An item can have multiple Genre Categories related to it, and those usually have a single choice from within their Category, but might have multiples.

      It's supporting the possible multiples that I'm trying to work out. At this point there's 15 possible Genre Categories that each Item could have a selection from. Most have 4-6 of them.

      Example 1: Items::Table on the left, Genre::Table on the right, with some Genre Categories (Genre, Perspective, Pacing, Gameplay, Interface, Setting) and their sub-options.
      This one has just one sub-option per category.

      Example 2:  The Gameplay Genre Category has two sub-options related to it.

      Example Genre Table content:

      Just cracking the surface on One to One and One to Many relationships, I don't think this scenario is quite covered this way.

      Would each of those Genre Categories be portals showing only their related category ID?

      I presume that I would add fields to the Items::Table so I can pull related records:
      But those only support one relationship.
      Would I make value lists from those Genre Categories and Platforms and set them as tick box fields?
    • By Tony Diaz
      How do I set a field with a value list item from a script, from a set of tick box values? .. or remove a value list item, but leave whatever else is there alone?
      Looks like GetValue with the name of the value list and the position # of the value, but I'm sure that also means that if I change that value list, I need to change any scripts that reference them by position # too..
      Something like detecting if a string is already there, and if not add it, surrounded by C/Rs, unless it's the first item entry?
      For the first time a field is set, I presume I could just Set Field (field ; "Value List Item"), and if there's only one entry/line in it, just clear it.  But if there's already a value there, I just want to select an additional one.
    • By Jonathan Myrah
      After completing an initial sync to my test iPad (starting with an empty clone) none of my value lists that reference fields are working. Instead I get a message saying "No Matches Found". Value lists with custom values still work.
      According to the email I received after the sync completed, all of the records required for the values lists were successfully synced.
      Has anyone seen this before?
    • By Ponderosa
      Totally not sure if I've put this thread in the right topic category, as I'm not even sure how to go about this. First, I'd like to be very clear on the fact that, if possible, this will be done without scripting. I'm hoping for some kind of calculation. IDK. Anyway, I'm trying to copy or Calc one field from an older record to its newest match (using BatchNum field to match). The two fields are different fields (FormNum is the ID number field) (RefID is the the reference field for FormNum ). RefID would allow the user to find the last time the same arrangement of data (FormNum being its ID number) was used. It's meant as sort of a skipping stone effect, if that makes sense.
      My thought was to create a filtered value list of some kind and just set RefID with the highest ID number from FormNum, prior to the newest entry. Or organize by newest Date Created, prior to the newest entry. Again, IDK.
      I'm quite stumped and hope this rambling makes sense to you very helpful people. Thanking you in advance....
    • By Lea Alexander
      I have 3 tables: Person, Session, and Contract where Contract is a join table.  
      I am in the Session table layout, in a Contract portal.  I have a field – Trainer Name in the Contract table – where I want to select the name of a person from a list of people in the Person table.  The Trainer Name field is a dropdown list using values from the Trainer value list “Person::__k_p_Person and Person::Name_Full”. I have tried numerous configurations with table instances and I either cannot alter the Trainer Name field at all, or I get a “?”.  
      After the Contract record is created, I want it to show up in a Contract portal on the person’s record in the Person table.  
      Each person will teach multiple sessions.
      Each session has multiple trainers.
      I have foreign keys in the Contract table for Person and Session. 

  • Who Viewed the Topic

    9 members have viewed this topic:
    chris7of9  pleiades  mr_vodka  Quito  LAIRY  bruceR  Steve Martino  Aussie John  comment 

  • Create New...

Important Information

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