1

Calling an image from another table

So, I am probably missing something very simple or having a complete brain fade!!

In my 'JobDockets' table, I have a button for sending out a goods dispatched email - which works great and also adds an advert attachment.... 

The attachment is stored in a table "SystemFile' where I keep central info/images/letters etc.

The image picked up is the first image in the first record (ie Advert1)

I also have a second image and I want to occasionally swop the image I send out. so I plan to do this by a pop up choice but for now in testing I can't get it to pick up the second image

I have tried calling the image by name 

let myAtt := first(files(record(SystemFile,Advert2))); 

and by offsetting the index number let myAtt := first(files(record(SystemFile,2)));

but neither of these work?

So at the moment I have to physically swop the images over 

Here is my very cutdown script just showing the essential lines

which picks up the appropriate letter and then collects the image from the main 

What am I missing?

            let thisBody := first((select SystemFile).OrderDispatchedLetter);
            let myEmail := userEmail();
            let myAtt := first(files(record(SystemFile,1)));
            thisBody := replace(thisBody, "{Ord Company}", text('Ordered By'));
            thisBody := replace(thisBody, "{Order Name}", text('Order Name'));
            etc 

            sendEmail({
                from: myEmail,
                to: 'Email+',
                subject: "Your order has been dispatched by Test Co",
                text: "Goods Dispatched Confirmation",
                html: thisBody,
                attachments: myAtt
            });

            end

As usual thank you in advance

Mel

9 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 1 yr ago
    • Reported - view

    Mel Charles  Try:

     let myAtt := item(files(record(SystemFile,1)),0);

    or if your attachment comes from a picture field (like your screenshot)

    let myAtt := first((select SystemFile).Advert1)

      • Mel_Charles
      • 1 yr ago
      • Reported - view

      RoSoft_Steven Thank you Sire - You are the man !! I was close but not close enough... both worked - however I will use the 2nd option and call the field name and wrap it in a pop up choice 🙂

      • Mel_Charles
      • 1 yr ago
      • Reported - view

      RoSoft_Steven I wonder if you can advice on the best approach to expand this

      so basically if I use month(today()) to generate a month number

      then what I want to do is as follows

      if the month number is either 1(jan), 4(april), 7(july) or 10(oct) then select up advert1 

      if month is 2,5,8 or 11 then select advert2 

      if month is 3,6,9,12 then select advert3

      so I get an auto roll of adverts but using the if-then statement it might get messy (and i will end up with a lot of spaghetti script! 

      example:

      if month(today()) = 1 or month(today()) = 4 or month(today()) = 7 or month(today()) = 10 then      let myAtt := first(files(record(SystemFile,1)));

      else if month(today()) = 2 or month(today()) = 5 or month(today()) = 8 or month(today()) = 11 then  let myAtt := first(files(record(SystemFile,2)));

      else if month(today()) = 3 or month(today()) = 6 or month(today()) = 9 or month(today()) = 12 then  let myAtt := first(files(record(SystemFile,3)));

      is there a better way to script this ? - I don't like the above and also even though it goes into the script editor it gives a 'void' statement after the first 2 script lines?

      • Ninox partner
      • RoSoft_Steven.1
      • 1 yr ago
      • Reported - view

      Mel Charles Something like:

      let adv1 := concat([1, 4, 7, 10]);
      let adv2 := concat([2, 5, 8, 11]);
      let adv3 := concat([3, 6, 9, 12]);
      let mtd := text(month(today()));
      let myAtt := switch true do
          case contains(adv1, mtd):
              first(files(record(SystemFile,1)))
          case contains(adv2, mtd):
              first(files(record(SystemFile,2)))
          case contains(adv3, mtd):
              first(files(record(SystemFile,3)))
          end;
      ....
      
      • Mel_Charles
      • 1 yr ago
      • Reported - view

      RoSoft_Steven Thanks that 's interesting - i have not used switch case before .. will get this method a try then....

      again thank you. ...always learning!  🙂

      • Ninox partner
      • RoSoft_Steven.1
      • 1 yr ago
      • Reported - view

      Mel Charles 👍Always be at your service with pleasure....

      • Mel_Charles
      • 1 yr ago
      • Reported - view

      RoSoft_Steven in that case can I indulge you further please....

      I have plugged in your code and get no errors and everything works but it is now no longer picking up the attachment..

      Have I missed something? full code here (also Ninox objects if i remove the "end" from your code block!

      if Status < 2 or Status > 3 then
          alert("job Status is not in production")
      else
          Status := 3;
          if not 'Email+' then
              alert("No valid contact in email field")
          else
              if DispFlag = 1 then
                  alert("Dispatched Confirmation already sent!")
              else
                  let thisBody := first((select SystemFile).OrderDispatchedLetter);
                  let myEmail := userEmail();
                  let adv1 := concat([1, 4, 7, 10]);
                  let adv2 := concat([2, 5, 8, 11]);
                  let adv3 := concat([3, 6, 9, 12]);
                  let mtd := text(month(today()));
                  let myAtt := switch true do
                      case contains(adv1, mtd):
                          first(files(record(SystemFile,1)))
                      case contains(adv2, mtd):
                          first(files(record(SystemFile,2)))
                      case contains(adv3, mtd):
                          first(files(record(SystemFile,3)))
                      end;
                  thisBody := replace(thisBody, "{Ord Company}", text('Ordered By'));
                  thisBody := replace(thisBody, "{Order Name}", text('Order Name'));
                  thisBody := replace(thisBody, "{Job Bag}", text('Job Bag'));
                  thisBody := replace(thisBody, "{Order Date}", text('Order Date'));
                  thisBody := replace(thisBody, "{Order Name}", 'Order Name');
                  thisBody := replace(thisBody, "{Order Ref}", 'Order Ref');
                  thisBody := replace(thisBody, "{Qty}", text(Qty));
                  thisBody := replace(thisBody, "{Desc}", Desc);
                  thisBody := replace(thisBody, "{Del Company}", text('Delivery To'));
                  thisBody := replace(thisBody, "{Del Address}", text('Delivery Address'));
                  DispFlag := 1;
                  sendEmail({
                      from: myEmail,
                      to: 'Email+',
                      subject: "Your order has been dispatched by Test Co",
                      text: "Goods Dispatched Confirmation",
                      html: thisBody,
                      attachments: myAtt
                  });
                  alert("Goods Dispatched Email has been Sent");
                  DisplayMenu := 1
              end
          end
      end

      • Ninox partner
      • RoSoft_Steven.1
      • 1 yr ago
      • Reported - view

      Mel Charles 

      We mixed the two methods for the attachments...

      it should be  item(files(record(SystemFile,1)),0) - item(files(record(SystemFile,1)),1) - item(files(record(SystemFile,1)),3) if you select the attachments from the first Setting record

      or first((select SystemFile).Advert1) - first((select SystemFile).Advert2) - first((select SystemFile).Advert3) if you select them from image-fields (also from the first Settings record).

      not a mix like:  first(files(record(SystemFile,1)))

      My bad 😖

      • Mel_Charles
      • 1 yr ago
      • Reported - view

      RoSoft_Steven ha ha- i see it now 

      Tested and works (BRILLIANT).

      This is so powerful - the manual version has been so responsive , so now I can roll the adverts with a condition it will be even greater.

      I owe you lunch buddy ! 🙂

Content aside

  • Status Answered
  • 1 Likes
  • 1 yr agoLast active
  • 9Replies
  • 164Views
  • 3 Following