Solved: Action question, Linktoform

Frank_McGee · 07-02-2020 11:10 AM

I have a form that collects a bunch of data from users, and occasionally a user has a need to create several records that are related (share values for several, but not all columns). I created an action using Linktoform, that copies the relevant fields to my “New” form view. Because I only want this action to fire if the person has related records to enter, I created a number column asking the user how many related records they’re needing to create. Then I created another hidden column with the initial value of the first column minus 1. I set my action to only fire if the value of the hidden column was greater than zero, and my link to form reduces the value of the hidden column by 1.

I tested this action manually by having it “Display Prominently”, it worked as expected. I then set my form saved action on the “New” view as this action. It worked as expected, and if a user entered a value greater than 1, it would fire automatically until the hidden column had been reduced to 0. I celebrated my success, and moved on to the next thing I needed to build/fix.

After I got done tweaking my next change, this action no longer fired automatically when the form was saved. When I tested it manually by having it displayed, it still worked fine, and when I removed the condition that action only fired if the value of my hidden column was greater than 0, it would fire automatically again. I completely undid the change I made after I had everything working (though the two shouldn’t affect each other), but I cannot get this action to fire automatically with the IF condition under behavior.

When I test the condition in the expression assistant it accurately returns Y/N’s. It’s a very simple expression [column]>0, and I’m struggling to figure out what’s going on. Anyone had a similar experience, can suggest what I might be overlooking, or have a different way of achieving the same result?

Thanks,
Frank

Frank_McGee

Found the solution in this thread, the workflow was firing before the child records had been added.

View solution in original post

Frank_McGee

Update…

If I fire the action manually the first time, it fires automatically, correctly, until my counter column gets down to 0. It still won’t fire automatically correctly the first time.

LeventK

May I ask why do you need such tricky ways? You just need:
1.) A YES/NO type column asking if they want to add related records or not i.e named as Choice
2.) A REF type column, referenced table as what-ever your related record table is and isPartOf property set to ON.
2.a.) Set the SHOW_IF, REQUIRED_IF and VALID_IF expressions for this ref column:

SHOW_IF & REQUIRED_IF

[Choice]=TRUE

VALID_IF

IF(
    [Choice]=TRUE,
    COUNT(
        SELECT(
            RelatedTableName[Key],
            [Key] = [_THISROW].[YourRefColumnName]
        )
    ) > 0,
    TRUE
)

Frank_McGee

Levent, I’ve not tried anything like what you’re talking about, but I’ll look into it a little more. Without some more digging I’m not sure it would work for me though. I have an app that’s collecting data from our field staff, and then a script is being used to capture that data into a legacy database used by our headquarters staff. I know zero about coding or scripting of any kind, so it may be simple enough for the folks handling that end of things to change the script to go find related records in another table. Unfortunately I have very little ability to influence things on that end, so I need to have each entry be a record in this table.

I actually thought this was a pretty straight forward way of allowing end users to copy some of the information from a previous record when creating a new one.

LeventK

@Frank_McGee
I assume you have 2 tables, right? One table for collecting data from field staff and another table that you launch a separate form to record if they have any related data (these are from your explanations above). Here is a proof of concept on how it works. Click on your name in the main menu below and it will mimic your form that you staff needs to fill. At the end of the form you will notice the choice option and after you record your related additional data in a related record form and save, you will turn back to the main form, save it and job is finished.

Frank_McGee

No, in my original app, I have lots of tables (it’s a pretty complicated app), but all of the records being collected by field staff show up as lines in one table, which after the fact is imported into a legacy database. When I said related, I meant in a non-table/database sense. I think I’m tracking your suggestion to accomplish the same thing using related tables, with child entries. So I’m going to try to build that (because I do think it’s simpler from a user perspective), and then I’ll hope the database folks in my agency will be able to support it.

LeventK

@Frank_McGee
You have mentioned that the database folks are using scripting. May I ask what language are they using? Assuming that your back-end in Google Spreadsheet they shall be using Google Apps Script, or provided you are using Excel as a back-end then they should probably be using VBA. So nevertheless, it won’t be a problem for them to retrieve the data.

Frank_McGee

I have no idea. My back-end is a Google Sheet, and they are using a SQL database of some kind. Appreciate your help. Still don’t understand why my action isn’t working though…

LeventK

Gotcha. But as I said, you don’t need that tricky action actually. Parent-child related tables will be the best route to follow.

Frank_McGee

Not sure if I should post this is a stand alone question, but since it’s related, I’ll put it here for now. My old app created a PDF of each record as it was created using a workflow and a Google Doc template. I’m trying to do the same thing with this new format of a parent record with multiple child records, and I’m having very little luck. I’ve tried a couple of different start expressions, and when I test them in the app, they work, but when I try them in the template, it returns all of the parent record information, but returns blank for child records. I’ve read every post I can find on here about this issue, it sounds like some people have suggested this occurs due to security filters or possibly a data caching issue?

Here’s the expression I’m trying to use in my template (my app is meant to capture biological data on hunter harvest animals)…

Bobcat Details
<<Start: ORDERBY([Related bobcats], [Date of Kill])>>
Seal: <<[Seal]>>
Date of Kill: <<[Date of Kill]>>
Method: <<[Method]>> <<[Method Other]>>
Sex: <<[Sex]>>
Age: <<[Age]>>
Animal Checked Was: <<[Animal Checked Was]>>
Location
Land Type: <<[Land Type]>>
GMU: <<[GMU]>>
County: <<[County]>>
Drainage: <<[Drainage]>>
Coordinates: <<[Zone]>> <<[Easting]>> <<[Northing]>><<[latlong text]>>
Comments: <<[Comments]>>
<<[Photo 1]>>
<<[Photo 2]>>
<>

Any thoughts or insight on what I might try differently would be appreciated. My app doesn’t have any security filters, and this issue occurs both when use an expression that tries to use my virtual column, and when I directly call on the key in the child table ( ORDERBY(SELECT(bobcats[Seal], IN([Seal], [Related bobcats])), [Date of Kill]))

Thanks.

Frank_McGee

Found the solution in this thread, the workflow was firing before the child records had been added.

Frank_McGee

I think I’ll build it out the way you’re suggesting too, and see if I can get them to support that…