5 Replies Latest reply on Nov 8, 2010 5:40 PM by WinstonChurchill

    field 1 = field 2 from other record

    WinstonChurchill

      Title

      field 1 = field 2 from other record

      Post

      Example; (all fields in same table)

      field A is an auto enter serial number,

      field B is lets say a boys name

      field C is initially an empty edit box, into which I intend entering one of any of the serial numbers (field A) already created in existing records.

      field D will be a calculation (I assume) that will display the boy's name (from field B) depending on the value set in field C.

      I assume I'm looking at a calculation that involves a script, which is new ground for me, or am I looking to overcomplicate something that's quite simple really.

        • 1. Re: field 1 = field 2 from other record
          WinstonChurchill

          Anyone ? I'm still struggling to work out how I do this.

          • 2. Re: field 1 = field 2 from other record
            LaRetta_1

            It depends upon why you are trying to do this ... it might suggest that you need another table, one that holds one each of the 'names' indicated by the 'boys name'. 

            We need real-life information and not field A or field B.  What is the table and what does the unique serial within it represent?  How is the boys name associated to each record within this table? You probably don't need script but rather proper relationships.  :^)

            • 3. Re: field 1 = field 2 from other record
              WinstonChurchill

              OK I'll try.

              As in the first (dummy example); field A is the unique serial number for the record.

              Field B is the name of an envelope (a surface to a room, i.e. a wall, floor or ceiling). The name of an envelope has to take the form "z2/06/sei.3" and is auto calculated from details previously and subsequently entered about which floor (z2), which zone (often a room) (06), which orientation the envelope faces (se), whether it's an internal or external wall (i) and the occurrence (.3) (in this example, the third south east internal wall in zone 6 on the second floor.

              In addition to having FM just calculate the envelopes name, I also have it calculate consecutive names (i.e. z2/06/sei, z2/06/sei.1, z2/06/sei.2 etc etc) (just in case you're wondering "", ".1" and ".2" are the correct additions for occurrences 1, 2 and 3 - the nomenclature I have to use is quite strict, but doesn't always follow common sense).

              In addition to it calculating names sequentially, I also have it auto apply one of two options when an envelope is deleted in the middle of a sequential list. By way of example, if I have 3 envelopes (z2/06/sei, z2/06/sei.1 and z2/06/sei.2) and delete "z2/06/sei.1", it will either leave "z2/06/sei.2" unaltered so I can create a new "z2/06/sei.1" to fill the gap left by the one I previously deleted, or it will rename "z2/06/sei.2" to "z2/06/sei.1" and shuffle the names of all other envelopes down to suit.

              I'm not sure how much of all that is relevant, but for a newbie like me the calculations and scripts used to achieve all that seem complex and I didn't want to fiddle with what I've done already too much.

              Next up, I have to associate glazing and doors with each envelope (if they have them), which I have done through a portal. Now although I have used a portal I am only allowed to enter one type of glazing and one type of door per envelope. (Doing it through a portal lets me enter several windows/doors of differing sizes but the same glazing/door type and calculates a single glazing/door area value for them all in total in the parent table)

              If an envelope has two types of glazing/door, then I have to create another envelope (but leave all but glazing/door details blank) to enter the second type of glazing/door. This new envelope needs to have the same name as the first envelope so that the nomenclature for the glazing/door is correct (ie two types of glazing in "z2/06/sei.2" should be "z2/06/sei.2/g" and "z2/06/sei.2/g.1"). The name of the glazing and the doors is also auto calculated and provides the same options should I delete a glazing/door element as the options discussed previously when I delete an envelope.

              I have all this working except when I need to create a second envelope with the same name as another, basically the scripts and calculations I have used only work with unique names. In working at a solution to allow duplicate envelope names and still have what I have already done work, I also have to consider that the second duplicate envelope name needs to remain associated with the first.

              By way of example; if I have three envelopes (z2/06/sei, z2/06/sei.1 and z2/06/sei.2) and the last has two instances because it has two types of glazing (z2/06/sei.2/g and z2/06/sei.2/g.1) and I delete envelope "z2/06/sei.1", then both "z2/06/sei.2' envelopes need to be renamed "z2/06/sei.1" and both glazing occurrences also need to be renamed "z2/06/sei.1/g" and "z2/06/sei.1/g.1"

              The solution I have arrived at is to have the names of duplicate envelopes be calculated in a different way to the first occurrence of an envelope and to have them ending with a "+" (so the second occurrence of "z2/06/sei.2", would be "z2/06/sei.2+"), this would allow first occurrence names to remain unique and the calculated names for glazing/doors would just drop the "+" at the calculation stage. Second (and third) occurrence envelope names wouldn't need to be calculated the same way as first occurrence names, they could simply steal the name from the associated first occurrence and add the "+".

              My method of associating second occurrences with first is simply to have a field in each envelope record with two values ("" and "∂") which alternate when touched (working on an iPad). When set to "∂" another portal (using a table related to itself) displays all other envelopes in the same zone with the same orientation and allows me to touch one and take its serial number (which will always remain the same where it's name value may not) and store it in a new hidden field (field C in my dummy example) in the current record.

              Further to this I have also created a second new hidden field (field D in my dummy example) where I would like to have FM calculate its value using the 'current' envelope name from the record (first occurrence envelope) that has the serial number which matches the value in the first hidden field (C). This means that if that records envelope name changes (first occurrence record) so does the value of hidden field C. I would then be able to use field C in my calculation for the second occurrence envelope name and simply introduce a "Case (field C = "∂"......) into my existing calculations for envelope names as a whole and have first and second occurrence envelope names calculated differently.

              Again, I have all this working except that having set the new field to the value of the serial number for the associated envelope, I have no idea how to get FM to set/calculate the value for the second hidden field (D).

              Just in case all this is confusing, going back to my dummy example:

              if I have the following records say,

              A = NM001, B = "Harry", C = "", D = ""

              A = NM002,  B = "Tom",  C = "",  D = ""

              A = NM003,  B = "George",  C = "",  D = ""

              A = NM004,  B = "Cecil",  C = "",  D = ""

              How can I get record NM004 to display "Harry" in field D, when I enter NM001 into record NM004 field C. It's that calculation I don't know how to go about defining.

              • 4. Re: field 1 = field 2 from other record
                WinstonChurchill

                my DB

                I don't know if this will help:

                The portal in the middle on the top row shows other envelopes in the same zone with the same orientation as the envelope in the record I'm currently viewing. set the button to "+" (I mentioned "∂" before) and touch an envelope in the portal, it grabs it's serial number and puts it in hidden field C (not hidden on example but shown in red) (this portal uses a self relationship to the table used by the form)

                Second portal lets me measure between walls and auto adds 1/2 thickness of the wall on each side and or another wall (2 walls can make a single wall in my weird world) and calculates the total length and puts it in the near top left length field (can also override the auto calc by setting the "C" button to "M" and entering total length manually.

                Third portal is the glazing portal where I can add up all occurrences of similar type glazing either by entering length x width or just the area of one glazing unit.

                Third portal is the door portal, it does the same as the glazing portal (indeed it uses the same table)

                Excuse the word "Sid" in the nomenclature (I've typed in "Sid" into my second hidden field (D), to check all my auto calcs will work once I replace it with the calculation to display the name from the record with the serial number that's in the first hidden field (C)

                • 5. Re: field 1 = field 2 from other record
                  WinstonChurchill

                  Boy, how good that feels.

                  Whilst I don't dismiss your guidance LaRetta (Many Thanks), I wasn't just handed the answer this time and eventually worked it out for myself. I also appreciate that when I look at the solution now it's far removed from complicated, but it was just that I'd never used that function before and wasn't aware of what it did. Big learning curve on scripts/calculations ahead me now thinks.

                  I now have a bit more confidence, just have to see how it goes from here on in, I'm a fairly logical person but my age means my memory isn't what it was, I might be able to work things out (slowly), but remembering what all the functions and script steps do will be a challenge. I have the reference pdfs, but they're a long read when you don't know what you're looking for.

                  Solution:

                  (Parent table field C related to duplicate occurrence table field A), Parent::Field D = Lookup (duplicate occurrence::Field B)