Suggestions Please (select bulk rows and insert elsewhere)

AppSheet AppSheet Q&A
1

I’m building an app to manage a pipeline of loans a loan officer may be handling.

The relevant tables to this question are as follows.

TABLE RELATIONSHIPS

LOAN
LOAN ITEMS (LOAN is parent table, related by LOAN ID in this table)
LOAN ITEMS TEMPLATES (this table has no relationships.)

TABLE DESCRIPTIONS

LOAN (has all the relevant information about the loan: name, address, loan type, etc)
LOAN ITEMS (this has the checklists that are relevant to the LOAN its associated to.)
LOAN ITEMS TEMPLATES (This table holds a list of checklist items that are relevant to a given loan type)

LOAN ITEMS is basically an EAV table. We track status on each of these items. Its columns are like this…
ID, LOAN TYPE, CHECKLIST ITEM, STATUS, START DATE, END DATE

So one LOAN TYPE may have 50 entries in LOAN ITEMS TEMPLATES and another LOAN TYPE may have 70 entries in LOAN ITEMS TEMPLATES

PROBLEM STATEMENT

When I create a LOAN record, I then want to populate my LOAN ITEMS table with the relevent LOAN ITEMS TEMPLATES items based on the LOAN TYPE I selected when I created the loan.

PSEUDOCODE - Here is the logic I want to implement and I’m looking for the community to point me in the right direction. I can share my existing app if need be, right?

  • Click Default Items ACTION from LOAN card
  • Popup appears
  • select loan type of Purchase or REFI
    • If Purchase select FHA or VA or CONV or USDA
      • select * from LOAN ITEMS TEMPLATES where FHA column = TRUE
      • select * from LOAN ITEMS TEMPLATES where VA column = TRUE
      • select * from LOAN ITEMS TEMPLATES where CONV column = TRUE
      • select * from LOAN ITEMS TEMPLATES where USDA column = TRUE
        - Now map fields from LOAN ITEMS TEMPLATES to LOAN ITEMS and push to LOAN ITEMS using LOAN.ID as LOAN ITEMS.ID
    • If REFI
      • Select * from LOAN ITEMS TEMPLATES where REFI column = TRUE
        - Now map fields from LOAN ITEMS TEMPLATES to LOAN ITEMS and push to LOAN ITEMS using LOAN.ID as LOAN ITEMS.ID
  • Click Optional Items ACTION from LOAN card
  • Popup appears
  • Select loan type of Purchase or REFI
    • If purchase then select * from LOAN ITEMS TEMPLATES where Optional – Purchase = TRUE
      • Now I want to be presented with these as rows in a table and I want to select them with checkboxes.
        - For the ones I selected, now map fields from LOAN ITEMS TEMPLATES to LOAN ITEMS and push to LOAN ITEMS using LOAN.ID as LOAN ITEMS.ID
    • If REFI then select * from LOAN ITEMS TEMPLATES where Optional – REFI = TRUE
      • Now I want to be presented with these as rows in a table and I want to select them with checkboxes.
        - For the ones I selected, now map fields from LOAN ITEMS to LOAN ITEMS and push to LOAN ITEMS using LOAN.ID as LOAN.ID
2

Welcome to the community!

I’m having difficulties understanding your data structure. Could you please share your existing source sheet or a meaningful sample of it? Thanks.

3

the mapping during the process would be…

loan.ID → loan items.loan ID
loan items templates.Order → loan items.Order
loan items templates.Req → loan items.Req
loan items templates.Status → loan items.Status
loan items templates.Stage → loan items.Stage
loan items templates.Group1 → loan items.Group1
loan items templates.Group2 → loan items.Group2
loan items templates.Loan Item → loan items.Loan Item
loan items templates.Description → loan items.Description

1 Like
4

This is what I had suspected actually, but didn’t want to jump to conclusions.

Your problem is here:

In a database you should avoid having list items in columns. You are missing a Loan Types table, where you should list all your possible loan types.

In the Templates table, you should replace the type columns with a single column called “Loan Type”. This column should be of type Enumlist base ref, and will point to the “Loan Types” table. Now for each item in the Templates table you should be able to choose the corresponding Loan Types in the App’s Form, or enter them manually in the sheet separating the items with a “,”.

The loan items table should reference the Loans table, and also the Templates table that should serve as the base for all possible items of a loan.

Here’s your app:

1 Like
5

Thank you for your response.

  1. When I clicked on your link, it said “error cloning app”, so I did not get to see what you did, so keep that in mind.

  2. Yes I understand that from a data modeling perspective it might seem incorrect that I have put each loan type as a column. I have done this intentionally. What I’m trying to do is make a templating system that is easy to modify by laymen, non-technical users. Many loans have the same loan items that need to be tracked. So I would like each item (Order Appraisal for example) to exist only once in the LOAN ITEMS TEMPLATES table. So when they want to add a trackable item that doesn’t exist, they just add to the LOAN ITEMS TEMPLATES table once and put an x or true or whatever in the appropriate type columns it applies to, then when they create a loan and select that loan type, everything with an x in that column is copied into the LOAN ITEMS table using the described mapping.

I hope I have clarified that the app never shows a screen of LOAN ITEMS TEMPLATES, except for maybe where optional items need to be selected as described in my original post in the 2nd popup. The app shows LOAN ITEMS.

I could put all loan types in a single column, however then there would be a minimum of 4 rows for “Order Appraisal” in LOAN ITEMS TEMPLATES. That would be fine for me, but a more confusing for users I think.

I did not get to see the app you put together, so I’m unclear if you handled the copying of rows from LOAN ITEMS TEMPLATES into the LOAN ITEMS TABLE.

6

I am following you that a loan types table would be useful for storing the types to use in logic rather than hard coding those types.

But I couldn’t see your project, so I’m unclear how you used it for the solution.

7

appsheeter:

  1. When I clicked on your link, it said “error cloning app”, so I did not get to see what you did, so keep that in mind.

Yeah, I just realized that and I don’t know why! The URL for the editor’s view eventually is redirected to the templates page. I hope other experienced members here can help @SkrOYC @Suvrutt_Gurjar please.

This is the URL in question: appsheet (dot) com /Template/AppDef?appName=CopiedeCopyofloan-5317163

Now in the meantime you can view the app here as a user, this one works:

appsheeter:

from a data modeling perspective it might seem incorrect

I clearly see it as incorrect as impeding you from having a functioning app.

appsheeter:

make a templating system that is easy to modify by laymen, non-technical users. (…)> So when they want to add a trackable item that doesn’t exist, they just add to the LOAN ITEMS TEMPLATES table once and put an x or true (…)> everything with an x in that column is copied into the LOAN ITEMS table using the described mapping.

All this is already achieved, the only difference is that users will have the x checkboxes vertically in a multi-choice drop-down list.

appsheeter:

the app never shows a screen of LOAN ITEMS TEMPLATES, except for maybe where optional items need to be selected

Adding a dataset to the app and correctly modelling it in the datasource is one thing, and showing it to the user is another. You have to think about each one separately, as they are two independent things. You CAN control with a deep level of granularity what data is shown to what user, when, and if at all.

appsheeter:

so I’m unclear if you handled the copying of rows from LOAN ITEMS TEMPLATES into the LOAN ITEMS TABLE.

This is automatically handled by AppSheet through referencing.

appsheeter:

I am following you that a loan types table would be useful for storing the types to use in logic

It is not about being useful unfortunately, but at least personally I don’t know another way to do it.

appsheeter:

rather than hard coding those types.

You’ll still be able to hardcode them if so you desire, there is no restriction here.

appsheeter:

But I couldn’t see your project, so I’m unclear how you used it for the solution.

I’m sorry, this is quite frustrating. I hope the more experienced friends I’ve mentioned can help us in this regard.

1 Like
8

Ok, I see you created the LOAN TYPES table and related it to LOAN ITEMS TEMPLATE.

I created a new loan, I gave it Nate Test as Lead ID. You can see it has not LOAN ITEMS associated to it right now.

How would I get an action to take all the records from LOAN ITEMS TEMPLATE with VA in the loan type column copied into the LOAN ITEMS table for the new loan I created?

9

Please read this excellent guide:

[Add any number of rows from any row within a table](https://community.appsheet.com/t/add-any-number-of-rows-from-any-row-within-a-table/22869) Tips & Tricks ?

Definitely a frequently-asked question: how do I add some number of rows to a table automatically? It’s possible, but it’s not easy. This article details an approach that can be accomplished entirely within AppSheet (no API use), within the app itself (no workflow use), and accommodates an arbitrary number of rows (no built-in count maximum). While I’ve tried to make the guide accessible for less-experienced app creators, make no mistake: this is an advanced technique. I hope you find this us…

1 Like
10

I haven’t fully investigated that post yet, but it looks like I should just use the AppSheet API to select records, map them and push them to another table. Or possibly just push the required logic to database or google sheets via API.

11

It says:

Add any number of rows from any row within a table

This article details an approach that can be accomplished entirely within AppSheet (no API use), within the app itself (no workflow use)

2 Likes
12

appsheeter:

I haven’t fully investigated that post yet

“At all” looks to be more accurate.